Re: DEFERRABLE NOT NULL constraint

Dean Rasheed wrote:
>> ISO/IEC 9075-2:2003 says:
>>
>> 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.

The SQL standard is usually as confusing as is still
compatible with correctness, but after rereading the whole chapter
I think that here it is self-contradictory.

The syntax rules support what you say:
- If I specify nothing at all, INITIALLY IMMEDIATE is implicit.
- Since INITIALLY IMMEDIATE is implicit and neither DEFERRABLE
nor NOT DEFERRABLE are specified, NOT DEFERRABLE is implicit.

But how does that go together with General Rule 2?
It does not say "if NOT DEFERRABLE is specified or implicit",
it says "if NOT DEFERRABLE is specified".

Anyway, that's a sideline; at any rate the standard requires
deferrable NOT NULL constraints.

Yours,
Laurenz Albe