Re: ALTER TABLE .... make constraint DEFERRABLE

On 3 June 2010 02:06, Bruce Momjian <bruce@momjian.us> wrote:
>> Also, foreign keys can't be defined that refer to a deferrable primary
>> key. That isn't mentioned at all in the manual with regard to the
>> DEFERRABLE clause, though it is mentioned in the FK section. You get
>> this error message
>> ERROR:  cannot use a deferrable unique constraint for referenced table
>>
>> The use case for this feature looks a little narrow at present. Can we
>> do something about usability?
>
> Not sure why that was a limitation.
>

That's in accordance with the SQL spec.

I didn't think of this case originally, but all sorts of complications
would arise if we were to allow FKs to refer to deferrable PKs. For
example, if there are 2 temporarily duplicated PKs, and you update one
of them, what would the FK's ON UPDATE actions do? I'm not convinced
there is any sensible answer to this question.

Regards,
Dean