SQL Anywhere database - Default of NULL needs to override default set in domain

Author

Default of NULL needs to override default set in domain

Andrew Giulinn

2005-06-24, 3:23 am

Hi all

How do I set a default of NULL on a column that is based on a domain with a
non-NULL default?

The problem is, as the Help says:
[color=darkred]
For columns that allow NULL values, specifying a NULL default is exactly the
same as not specifying a default at all.
<<

This means that the default set on the column is removed, leaving the way
clear for the domain's default to be used.

But I actually want NULL as the default for this column, not the domain's
default.

Any ideas?

Thanks

Andrew

Rob Waywell

2005-06-24, 1:24 pm

Two things:

1) Since you want this to be nullable, you shouldn't declare it using the
domain type that is NOT NULL since that will be misleading to anyone else
maintaining the application and database. From the design perspective this
should be a separate domain.

2) Once a column is defined from a domain type, any modifications made to it
after that are specific to the column, the domain type is only referenced
when you create the column, it won't prevent you from altering the column
definition directly.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"Andrew Giulinn" <NO.oscar.SPAM@dsa.com.au> wrote in message
news:42bb8be8$1@foru
ms-1-dub...
> Hi all
>
> How do I set a default of NULL on a column that is based on a domain with
> a non-NULL default?
>
> The problem is, as the Help says:
>
> For columns that allow NULL values, specifying a NULL default is exactly
> the same as not specifying a default at all.
> <<
>
> This means that the default set on the column is removed, leaving the way
> clear for the domain's default to be used.
>
> But I actually want NULL as the default for this column, not the domain's
> default.
>
> Any ideas?
>
> Thanks
>
> Andrew
>

Andrew Giulinn

2005-06-26, 8:23 pm

Thanks for the reply Rob.

I'm not sure that we are on the same page. I don't think that my problem
relates to whether the domain and/or column are nullable, but as to what the
default is.

The domain has a default set. The column, in all other respects matching
the real-world domain being represented by the domain and therefore most
sensibly defined based on that domain, needs a different default value. As
you say in your second point, if the domain has a default of 0 (zero) and I
need the column to have a default of 1 (one), that is fine, just set the
column to be based on the domain and then set the default for the column to
1 - thus the column will vary from the domain by that specific setting.

But what if the different default value that I want is NULL?

To illustrate (and check) my point, I created three tables (each with a
column based on my COUNT domain, which has a default value of 0) and
performed an insert into each, relying on the default for the column based
on the COUNT domain.

create table adltemp (
adltemp_id integer not null,
adltemp count not null,
primary key (adltemp_id));

insert into adltemp (adltemp_id) values (1);

THE RESULT: adltemp_id = 1, adltemp = 0. That is, the adltemp column has
been set using the default in the domain.

create table adltemp2 (
adltemp2_id integer not null,
adltemp2 count not null default null,
primary key (adltemp2_id));

insert into adltemp2 (adltemp2_id) values (1);

THE RESULT: adltemp_id = 1, adltemp = 0. That is, the adltemp column has
been set using the default in the domain, even though I explicitly
"overrode" this to say that I wanted NULL as the default (or, put another
way, no default) value for that column.

create table adltemp3 (
adltemp3_id integer not null,
adltemp3 count not null default 100,
primary key (adltemp3_id));

insert into adltemp3 (adltemp3_id) values (1);

THE RESULT: adltemp_id = 1, adltemp = 100. That is, the adltemp column has
been set using the default specified for the column, ie it "overrode" the
default in the domain.

My question is: how can I tell ASA to set adltemp = NULL in the second
example?

And, while I don't think it is on a topic relevant to my question (but am
willing to be convinced), I have some thoughts on your first comment:

I am interested in your first comment, given your second. This suggests
that, while other features of the domain can be "overridden" by subsequent
changes without being "misleading to anyone else maintaining the application
and database", discrepancies as to whether columns based on the domain are
mandatory or not is considered misleading. That is actually the "attribute"
that I would've expected least to be so restricted. I think of a domain as
modelling or representing a real-world concept. That concept can apply in
various situations, some where it is a required value and others whether it
is not required. I wouldn't have expected to have to create a whole new
domain, modelling/representing the same concept, just to have it required or
not - but not to have to do the same thing for other "attributes" of the
"datatype". Given my opposite thinking on this topic, am I missing an
important concept in the RDBMS/Modelling world?

Cheers

Andrew

"Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
news:42bc5322$1@foru
ms-2-dub...
> Two things:
>
> 1) Since you want this to be nullable, you shouldn't declare it using the
> domain type that is NOT NULL since that will be misleading to anyone else
> maintaining the application and database. From the design perspective this
> should be a separate domain.
>
> 2) Once a column is defined from a domain type, any modifications made to
> it after that are specific to the column, the domain type is only
> referenced when you create the column, it won't prevent you from altering
> the column definition directly.
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available through the
> iAnywhere
> Developer Community at www.ianywhere.com/developer
> "Andrew Giulinn" <NO.oscar.SPAM@dsa.com.au> wrote in message
> news:42bb8be8$1@foru
ms-1-dub...
>
>

Rob Waywell

2005-06-27, 11:23 am

this line doesn't seem to make any sense:

> adltemp2 count not null default null,

If the column does not allow NULL, then it can not be assigned a default of
NULL.

This sequence should do what you are attempting:

create domain george int default 0;
create table t1
(row_id int primary key default autoincrement,
row_count george default NULL
);
insert into t1(row_id) values (DEFAULT);
select * from t1 --> will show 1, 0

alter table t1 modify row_count default NULL
insert into t1(row_id) values (DEFAULT);
select * from t1 --> will show 1, 0
2, NULL

It does not appear that you can override the default of the domain in the
CREATE TABLE statement, you need to do that separately with an ALTER TABLE
statement after creating the column.

--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional

Sybase's iAnywhere Solutions

Please respond ONLY to newsgroup

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the iAnywhere
Developer Community at www.ianywhere.com/developer
"Andrew Giulinn" <NO.oscar.SPAM@dsa.com.au> wrote in message
news:42bf4443@forums
-1-dub...
> Thanks for the reply Rob.
>
> I'm not sure that we are on the same page. I don't think that my problem
> relates to whether the domain and/or column are nullable, but as to what
> the default is.
>
> The domain has a default set. The column, in all other respects matching
> the real-world domain being represented by the domain and therefore most
> sensibly defined based on that domain, needs a different default value.
> As you say in your second point, if the domain has a default of 0 (zero)
> and I need the column to have a default of 1 (one), that is fine, just set
> the column to be based on the domain and then set the default for the
> column to 1 - thus the column will vary from the domain by that specific
> setting.
>
> But what if the different default value that I want is NULL?
>
> To illustrate (and check) my point, I created three tables (each with a
> column based on my COUNT domain, which has a default value of 0) and
> performed an insert into each, relying on the default for the column based
> on the COUNT domain.
>
> create table adltemp (
> adltemp_id integer not null,
> adltemp count not null,
> primary key (adltemp_id));
>
> insert into adltemp (adltemp_id) values (1);
>
> THE RESULT: adltemp_id = 1, adltemp = 0. That is, the adltemp column has
> been set using the default in the domain.
>
> create table adltemp2 (
> adltemp2_id integer not null,
> adltemp2 count not null default null,
> primary key (adltemp2_id));
>
> insert into adltemp2 (adltemp2_id) values (1);
>
> THE RESULT: adltemp_id = 1, adltemp = 0. That is, the adltemp column has
> been set using the default in the domain, even though I explicitly
> "overrode" this to say that I wanted NULL as the default (or, put another
> way, no default) value for that column.
>
> create table adltemp3 (
> adltemp3_id integer not null,
> adltemp3 count not null default 100,
> primary key (adltemp3_id));
>
> insert into adltemp3 (adltemp3_id) values (1);
>
> THE RESULT: adltemp_id = 1, adltemp = 100. That is, the adltemp column
> has been set using the default specified for the column, ie it "overrode"
> the default in the domain.
>
> My question is: how can I tell ASA to set adltemp = NULL in the second
> example?
>
> And, while I don't think it is on a topic relevant to my question (but am
> willing to be convinced), I have some thoughts on your first comment:
>
> I am interested in your first comment, given your second. This suggests
> that, while other features of the domain can be "overridden" by subsequent
> changes without being "misleading to anyone else maintaining the
> application and database", discrepancies as to whether columns based on
> the domain are mandatory or not is considered misleading. That is
> actually the "attribute" that I would've expected least to be so
> restricted. I think of a domain as modelling or representing a real-world
> concept. That concept can apply in various situations, some where it is a
> required value and others whether it is not required. I wouldn't have
> expected to have to create a whole new domain, modelling/representing the
> same concept, just to have it required or not - but not to have to do the
> same thing for other "attributes" of the "datatype". Given my opposite
> thinking on this topic, am I missing an important concept in the
> RDBMS/Modelling world?
>
> Cheers
>
> Andrew
>
> "Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
> news:42bc5322$1@foru
ms-2-dub...
>
>

Andrew Giulinn

2005-06-27, 8:23 pm

Rob

Oops - you are right about that line. Sorry about that. No need for that
second column to be NOT NULL in any of those tables. The end result was the
same (since the DDL was never going to allow NULL anyway) but better to get
it right so we know what we are looking at.

Anyway, yes you are right. The important fact that I hadn't realised before
this conversation was the specifics of the relationship between column and
domain. You explained that when you said in your previous post: "the domain
type is *only* referenced when you create the column". So a subsequent
ALTER TABLE statement, setting the default to NULL, "overrides" the domain.
Accordingly, the sequence you gave me works just fine.

Now, my only problem is: how to get PowerDesigner to do this automatically,
ie without having to hand-edit the SQL script created to modify a database,
to include such ALTER TABLE statements. I'll go and have a look into that
now.

Thanks very much for your help and ideas.

Cheers

Andrew

"Rob Waywell" <rwaywell@no_spam.ianywhere.com> wrote in message
news:42c01bcd$1@foru
ms-1-dub...
> this line doesn't seem to make any sense:
>
>
> If the column does not allow NULL, then it can not be assigned a default
> of NULL.
>
> This sequence should do what you are attempting:
>
> create domain george int default 0;
> create table t1
> (row_id int primary key default autoincrement,
> row_count george default NULL
> );
> insert into t1(row_id) values (DEFAULT);
> select * from t1 --> will show 1, 0
>
> alter table t1 modify row_count default NULL
> insert into t1(row_id) values (DEFAULT);
> select * from t1 --> will show 1, 0
> 2, NULL
>
> It does not appear that you can override the default of the domain in the
> CREATE TABLE statement, you need to do that separately with an ALTER TABLE
> statement after creating the column.
>
> --
> -----------------------------------------------
> Robert Waywell
> Sybase Adaptive Server Anywhere Developer - Version 8
> Sybase Certified Professional
>
> Sybase's iAnywhere Solutions
>
> Please respond ONLY to newsgroup
>
> EBF's and Patches: http://downloads.sybase.com
> choose SQL Anywhere Studio >> change 'time frame' to all
>
> To Submit Bug Reports:
> http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
>
> SQL Anywhere Studio Supported Platforms and Support Status
> http://my.sybase.com/detail?id=1002288
>
> Whitepapers, TechDocs, and bug fixes are all available through the
> iAnywhere
> Developer Community at www.ianywhere.com/developer
> "Andrew Giulinn" <NO.oscar.SPAM@dsa.com.au> wrote in message
> news:42bf4443@forums
-1-dub...
>
>

Sponsored Links