Обсуждение: null constraints and defaults

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

--elein
elein@varlena.com

On Wed, 2005-06-29 at 13:04 -0700, elein wrote:
> default constraint
> ...
> elein=# insert into nulldefault values (NULL);
> ERROR:  null value in column "one" violates not-null constraint

I think the idea of a DEFAULT value is to tell the DB what to supply
only if you omit the column from the insert statement altogether.  If
you deliberately call for a NULL, the DB will try to oblige.

Thanks.  I understand that much.  I want to know if this
is the intended behaviour.

--elein

On Wed, Jun 29, 2005 at 08:25:09PM +0000, Matt Miller wrote:
> On Wed, 2005-06-29 at 13:04 -0700, elein wrote:
> > default constraint
> > ...
> > elein=# insert into nulldefault values (NULL);
> > ERROR:  null value in column "one" violates not-null constraint
>
> I think the idea of a DEFAULT value is to tell the DB what to supply
> only if you omit the column from the insert statement altogether.  If
> you deliberately call for a NULL, the DB will try to oblige.
>

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).

On Wed, Jun 29, 2005 at 01:46:46PM -0700, elein wrote:

[reformatted]

> On Wed, Jun 29, 2005 at 08:25:09PM +0000, Matt Miller wrote:
> > On Wed, 2005-06-29 at 13:04 -0700, elein wrote:
> > > default constraint
> > > ...
> > > elein=# insert into nulldefault values (NULL);
> > > ERROR:  null value in column "one" violates not-null constraint
> >
> > I think the idea of a DEFAULT value is to tell the DB what to supply
> > only if you omit the column from the insert statement altogether.  If
> > you deliberately call for a NULL, the DB will try to oblige.

> Thanks.  I understand that much.  I want to know if this
> is the intended behaviour.

Yes; if you want the default value, use DEFAULT instead of NULL.  If it
didn't work that way, how would you insert a NULL in the column?

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Aprende a avergonzarte más ante ti que ante los demás" (Demócrito)