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