Column not null with default null

[Arun Srinivasan]

"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

[Frederik]

Post by Arun Srinivasan
"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

--
Frederik Engelen

[Serge Rielau]

Post by Frederik

Post by Arun Srinivasan
"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

alter table tabname alter column colname drop not null

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0000888.htm

column-alteration

|--column-name-------------------------------------------------->

Post by Frederik
--+-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-----------------------------------'

[Arun Srinivasan]

Post by Frederik

Post by Arun Srinivasan
"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

alter table tabname alter column colname drop not null

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2....
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

Crap. I was browsing v8 docs.
Sorry and thanks Serge/Fred

[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
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--]

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? <<

I think you get slightly different error messages -- NOT NULL versus
DEFAULT error codes. That might be important if you are catching
errors.

[Lennart]

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? <<

I think you get slightly different error messages  -- NOT NULL versus
DEFAULT error codes. That might be important if you are catching
errors.

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:

[***@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]

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;

[Lennart]

Post by 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;

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]

Post by Lennart

Post by 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;

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.

I thought the thread started of on usefulness of a NULL default in a not
null column.

Cheers
Serge

[Lennart]

Post by Serge Rielau

Post by Lennart

Post by 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;

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

[Lennart]

Post by Arun Srinivasan
"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

[...]