Column not null with default null
Arun Srinivasan
I was able to create the table with above data type. Whoa, isn't this
wrong according to the 'spirit' of the condition not null? Someone
says he doesnt need any nulls but gives default null!!
But the question is, how do I convert a not null column to nullable ?
Please help. I've gone through the db2 info center reg the alter table
statement. No Joy.
Thanks
Arun
Frederik
He is right, I can't find it in the documentation either. Here it is:
alter table tabname alter column colname drop not null
--
Frederik Engelen
Serge Rielau
column-alteration
|--column-name-------------------------------------------------->
>--+-SET--+-DATA TYPE--| altered-data-type |-----------+----+---|
| +-| generated-column-spec |------------------+ |
| +-EXPRESSION AS--(--generation-expression--)-+ |
| +-INLINE LENGTH--integer---------------------+ |
| '-NOT NULL-----------------------------------' |
+-+-| generation-alteration |--| identity-alteration |-+-+
| '-| identity-alteration |----------------------------' |
+-DROP--+-IDENTITY---+-----------------------------------+
| +-EXPRESSION-+ |
| +-DEFAULT----+ |
| '-NOT NULL---' |
+-ADD SCOPE--+-typed-table-name-+------------------------+
| '-typed-view-name--' |
+-COMPRESS--+-SYSTEM DEFAULT-+---------------------------+
| '-OFF------------' |
+-SECURED WITH--security-label-name----------------------+
'-DROP COLUMN SECURITY-----------------------------------'
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Arun Srinivasan
> Frederik wrote:
> > On Mar 30, 9:28 am, Arun Srinivasan <arunro...@gmail.com> wrote:
> >> "MA_REMIT_DT" DATE NOT NULL WITH DEFAULT NULL
>
> >> I was able to create the table with above data type. Whoa, isn't this
> >> wrong according to the 'spirit' of the condition not null? Someone
> >> says he doesnt need any nulls but gives default null!!
>
> >> But the question is, how do I convert a not null column to nullable ?
> >> Please help. I've gone through the db2 info center reg the alter table
> >> statement. No Joy.
>
> >> Thanks
> >> Arun
>
> > He is right, I can't find it in the documentation either. Here it is:
>
> > alter table tabname alter column colname drop not null
>
>
> column-alteration
>
> |--column-name-------------------------------------------------->
>
> >--+-SET--+-DATA TYPE--| altered-data-type |-----------+----+---|
> | +-| generated-column-spec |------------------+ |
> | +-EXPRESSION AS--(--generation-expression--)-+ |
> | +-INLINE LENGTH--integer---------------------+ |
> | '-NOT NULL-----------------------------------' |
> +-+-| generation-alteration |--| identity-alteration |-+-+
> | '-| identity-alteration |----------------------------' |
> +-DROP--+-IDENTITY---+-----------------------------------+
> | +-EXPRESSION-+ |
> | +-DEFAULT----+ |
> | '-NOT NULL---' |
> +-ADD SCOPE--+-typed-table-name-+------------------------+
> | '-typed-view-name--' |
> +-COMPRESS--+-SYSTEM DEFAULT-+---------------------------+
> | '-OFF------------' |
> +-SECURED WITH--security-label-name----------------------+
> '-DROP COLUMN SECURITY-----------------------------------'
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab
Sorry and thanks Serge/Fred
Lennart
> "MA_REMIT_DT" DATE NOT NULL WITH DEFAULT NULL
>
> I was able to create the table with above data type. Whoa, isn't this
> wrong according to the 'spirit' of the condition not null? Someone
> says he doesnt need any nulls but gives default null!!
>
> But the question is, how do I convert a not null column to nullable ?
Are you sure you want this instead of dropping the default constraint?
/Lennart
[...]
Serge Rielau
NOT NULL column.
DEFAULT is chosen to supplement a value if none was specified by the
user BEFORE the modification.
NOT NULL is a constraint that is checked AFTER a modification
Inbetween there are BEFORE TRIGGERs.
So a before trigger may right all the wrongs of the default value. :-)
So there is true function provided here.
Cheers
Serge
Lennart
> OH, I forgot to comment on teh spirit of allowing a DEFAULT NULL for a
> NOT NULL column.
Serge, is there a difference in how DB2 treats
...c1 int default null not null
and
...c1 int not null
? To me they look logically equal, and therefor it makes sense that
the construction is legal.
/Lennart
[...]
--CELKO--
...c1 INTEGER DEFAULT NULL NOT NULL
.. c2 INTEGER NOT NULL
...c3 INTEGER DEFAULT 42 NOT NULL
CONSTRAINT foo CHECK (c3 < 40)
To me they look logically equal, and therefor it makes sense that the
construction is legal. <<
I think it is legal in Standard SQL, but it is like an endless loop or
a division by zero. Consider this statement:
INSERT INTO Foobar DEFAULT VALUES;
I put a NULL (the default DEFAULT) into c2,
I get a constraint violation when I try to put a NULL ( the declared
DEFAULT) into c1.
I get a constraint foo violation when I try to put 42 ( the declared
DEFAULT) into c3.
Lennart
> ...c1 INTEGER DEFAULT NULL NOT NULL
> .. c2 INTEGER NOT NULL
> ...c3 INTEGER DEFAULT 42 NOT NULL
> CONSTRAINT foo CHECK (c3 < 40)
>
> To me they look logically equal, and therefor it makes sense that the
> construction is legal. <<
>
> I think it is legal in Standard SQL, but it is like an endless loop or
> a division by zero. Consider this statement:
>
> INSERT INTO Foobar DEFAULT VALUES;
>
> I put a NULL (the default DEFAULT) into c2,
this will lead to a constraint violation as well, no?
> I get a constraint violation when I try to put a NULL ( the declared
> DEFAULT) into c1.
> I get a constraint foo violation when I try to put 42 ( the declared
> DEFAULT) into c3.
My point was that c1 and c2 will behave the same, i.e. even though the
declaration of c1 looks fishy it behaves the same way as c2. Agreed?
/Lennart
--CELKO--
I think you get slightly different error messages -- NOT NULL versus
DEFAULT error codes. That might be important if you are catching
errors.
Lennart
db2 9.5 for luw gives SQL0407N in both cases:
SQL0407N Assignment of a NULL value to a NOT NULL column
"TBSPACEID=2, TABLEID=37, COLNO=0" is not allowed. SQLSTATE=23502
But SQL0407N covers both cases:
[db2inst1@faramir ~]$ db2 "? SQL0407N"
SQL0407N Assignment of a NULL value to a NOT NULL column "<name>" is
not allowed.
Explanation:
One of the following occurred:
* The update or insert value was NULL, but the object column was
declared as NOT NULL in the table definition. Consequently:
* NULL values cannot be inserted into that column.
* An update cannot set values in that column to NULL.
* A SET transition-variable statement in a trigger cannot set
values
in that column to NULL.
* The update or insert value was DEFAULT, but the object column was
declared as NOT NULL without WITH DEFAULT in the table definition.
Consequently:
* A default value of NULL cannot be inserted into that column.
* An update cannot set default values of NULL in that column.
* A SET transition-variable statement in a trigger cannot set
default values of NULL in that column.
[...]
so perhaps there is a difference after all
/Lennart
Serge Rielau
CREATE TABLE T(c1 INT, c2 INT NOT NULL DEFAULT NULL);
CREATE TRIGGER trg BEFORE INSERT ON T REFERENCING NEW AS N FOR EACH ROW
WHEN (n.c2 IS NULL)
SET n.c2 = n.c1 + 1;
INSERT INTO T(c1, c2) VALUES (1, 10), (2, 20);
INSERT INTO T(c1) VALUES (3), (4);
SELECT * FROM T ORDER BY c1;
Lennart
> Try this (untested):
> CREATE TABLE T(c1 INT, c2 INT NOT NULL DEFAULT NULL);
> CREATE TRIGGER trg BEFORE INSERT ON T REFERENCING NEW AS N FOR EACH ROW
> WHEN (n.c2 IS NULL)
> SET n.c2 = n.c1 + 1;
>
> INSERT INTO T(c1, c2) VALUES (1, 10), (2, 20);
>
> INSERT INTO T(c1) VALUES (3), (4);
>
> SELECT * FROM T ORDER BY c1;
>
Serge, I'm not sure what this example should show. My concern was
whether
CREATE TABLE T(c2 INT NOT NULL DEFAULT NULL);
and
CREATE TABLE T(c2 INT NOT NULL);
will behave different under some circumstances?
/Lennart
Serge Rielau
I thought the thread started of on usefulness of a NULL default in a not
null column.
Cheers
Serge
Lennart
> Lennart wrote:
> > On 2 Apr, 12:34, Serge Rielau <srie...@ca.ibm.com> wrote:
> >> Try this (untested):
> >> CREATE TABLE T(c1 INT, c2 INT NOT NULL DEFAULT NULL);
> >> CREATE TRIGGER trg BEFORE INSERT ON T REFERENCING NEW AS N FOR EACH ROW
> >> WHEN (n.c2 IS NULL)
> >> SET n.c2 = n.c1 + 1;
>
> >> INSERT INTO T(c1, c2) VALUES (1, 10), (2, 20);
>
> >> INSERT INTO T(c1) VALUES (3), (4);
>
> >> SELECT * FROM T ORDER BY c1;
>
> > Serge, I'm not sure what this example should show. My concern was
> > whether
>
> > CREATE TABLE T(c2 INT NOT NULL DEFAULT NULL);
>
> > and
>
> > CREATE TABLE T(c2 INT NOT NULL);
>
> > will behave different under some circumstances?
>
> There is no difference that I am aware of.
>
Ok
/Lennart