Re: DEFERRABLE NOT NULL constraint

On 7 February 2013 07:45, Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> wrote:
> 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.
>

No. If you look at the Syntax Rules section just above that, it says:

1) If <constraint check time> is not specified, then INITIALLY
IMMEDIATE is implicit.
2) Case:
a) If INITIALLY DEFERRED is specified, then:
i) NOT DEFERRABLE shall not be specified.
ii) If DEFERRABLE is not specified, then DEFERRABLE is implicit.
b) If INITIALLY IMMEDIATE is specified or implicit and neither
DEFERRABLE nor NOT
DEFERRABLE is specified, then NOT DEFERRABLE is implicit.

So NOT DEFERRABLE is the default, if nothing else is specified.

That's actually a sensible default, because there are consequences to
making a constraint deferrable --- it can hurt performance if a large
number of rows need to be queued up for later checking, and also a
deferrable primary key/unique constraint can't be used as the target
for a foreign key.

Regards,
Dean