Re: DEFERRABLE NOT NULL constraint

Gavan Schneider wrote:
> Taking a different tangent ...

Good idea.

> Is there anything in the SQL standards about NOT NULL
> constraints being deferrable?
> 
> To my mind we should not consider implementing non-standard
> behaviour, but if something is in the standard I can't see why
> it shouldn't be implemented, esp. when there is no compulsion
> for it to be used.

ISO/IEC 9075-2:2003 says:

Chapter 11.4 (<column definition>):

<column constraint definition> ::=
  [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]

<column constraint> ::=
    NOT NULL
  | <unique specification>
  | <references specification>
  | <check constraint definition>

Chapter 10.8 (<constraint name definition> and <constraint characteristics>):

<constraint characteristics> ::=
    <constraint check time> [ [ NOT ] DEFERRABLE ]
  | [ NOT ] DEFERRABLE [ <constraint check time> ]

<constraint check time> ::=
    INITIALLY DEFERRED
  | INITIALLY IMMEDIATE


So yes, the standard caters for deferrable NOT NULL constraints.

Moreover:

Chapter 10.8, General Rules
1) A <constraint name> identifies a constraint. Let the identified constraint be C.
2) If NOT DEFERRABLE is specified, then C is not deferrable; otherwise it is deferrable.

So deferrable should be the default.

Yours,
Laurenz Albe