Bernard Dhooghe wrote:
Quote:
Environment: DB2 UDB LUW 8.2
>
Following problem was submitted.
>
create table t1 (c1 char(10) not null)
>
insert into t1 values(default)
>
This will result in (db2 command line):
>
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=2,
>
TABLEID=7606, COLNO=0" is not allowed. SQLSTATE=23502
>
>
So the default of an insert is NULL in this case but it is not allowed
and the explanation of the error is quite clear:
>
"
o 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.
>
"
>
Stretching this to it's limits:
>
should db2 allow to create a table not null without adding a default if
it assumes that the default will be NULL
No, there is no reason to be so restrictive there. Your applications could
always provide a value to be inserted, so that the NOT NULL constraint has
no effect on the apps.
Quote:
or
>
is this a nice trick of db2 to avoid to insert a default (kind of: no
default clause but only possible for not null columns).
Default and NOT NULL are two different, orthogonal things. If you don't
specify an explicit default, then NULL is used. If that doesn't match with
your table definition, it is up to you how to handle it (in the app or via
triggers).
Quote:
or
>
should there not be a clause: 'no default' , the real trouble is that
in case of static SQL (where the problem submission started from) , the
binder does not detect the statement will fail at runtime.
"No default" would mean (to me) that there is "no default value". An the
absence of a value is represented by NULL. So you will be exactly at the
same point again.
--
Knut Stolze
DB2 Information Integration Development
IBM Germany
