Re: Is this logical?

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Sun, 10 Sep 2006, Behrang Saeedzadeh wrote:
>> Shouldn't this create statement trigger an error?
>> create table bar (col1 int not null default null);

> I think it should forbid it when the default actually comes into play like
> on insert or update, but not at create table time as there are no rows for
> which the constraint fails.

The SQL spec defines the not-null clause as a check constraint:

a) If a <column constraint definition> is specified that
contains the <column constraint> NOT NULL, then it is
equivalent to the following <table constraint definition>:

CND CHECK ( C IS NOT NULL ) CA

It doesn't say anything about a nonnull default being required. In fact,
since what you wrote is exactly equivalent to

create table bar (col1 int not null);

(that is, "default null" is the default), you would have to say that NOT
NULL requires a non-null DEFAULT to be specified, and that is certainly
not in the spec; indeed NOT NULL with no default is a customary usage to
be sure that people specify a required column during INSERT.

regards, tom lane