Re: null constraints and defaults

On Wed, 29 Jun 2005, elein wrote:

> It seems that the not null constraint is applied
> before the default constraint if a column is
> defined with both default and not null.
>
> I understand that default should make the
> the NOT NULL constraint unnecessary. But still....
>
> Is this the intended behaviour?
>
>
> create table nulldefault ( one integer NOT NULL default 1 );
> CREATE TABLE
> ^
> elein=# insert into nulldefault values (NULL);
> ERROR: null value in column "one" violates not-null constraint
>
> elein=# create table defaultnull (one integer default 1);
> CREATE TABLE
> elein=# insert into defaultnull values (NULL);
> INSERT 4681559 1

I read SQL92 13.8 as saying the above is correct. Paraphrased: For each
row out of the query expression to insert, make a row of defaults, replace
the value each column of that row in the insert column list (in this case
the automatic one of all columns) with the value from the row (in this
case NULL).

So, saying values (NULL) means that you are not inserting the default, but
instead explicitly asking for a NULL value to be inserted. In the first
case this fails because of the constraint, in the second it succeeds and a
NULL should be inserted (not a 1).