Stephan Szabo <sszabo@megazone.bigpanda.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