Обсуждение: Deferred uniqueness versus foreign keys

[sigh, forgot to cc hackers the first time ]

Foreign key behavior is only sane if the referenced column(s) are
unique.  With the proposed patch, it is possible that the uniqueness
check on the referenced columns is deferred, which means it might not
occur till after an FK check does.  Discuss.
        regards, tom lane

2009/7/28 Tom Lane <tgl@sss.pgh.pa.us>:
> [sigh, forgot to cc hackers the first time ]
>
> Foreign key behavior is only sane if the referenced column(s) are
> unique.  With the proposed patch, it is possible that the uniqueness
> check on the referenced columns is deferred, which means it might not
> occur till after an FK check does.  Discuss.
>
>                        regards, tom lane
>

Hmm, yes, looking in the SQL spec, I've just noticed this under 11.8,
referential constraint definition:

"The table constraint descriptor describing the <unique constraint
definition> whose <unique column list> identifies the referenced
columns shall indicate that the unique constraint is not deferrable."

which seems like a sensible policy now that I think about it.
- Dean

On Tue, 2009-07-28 at 22:10 +0100, Dean Rasheed wrote:
> Hmm, yes, looking in the SQL spec, I've just noticed this under 11.8,
> referential constraint definition:
> 
> "The table constraint descriptor describing the <unique constraint
> definition> whose <unique column list> identifies the referenced
> columns shall indicate that the unique constraint is not deferrable."
> 
> which seems like a sensible policy now that I think about it.

Is it a problem to allow unique constraints to be deferrable until the
end of the command though? To meet the spec, "SET i = i + 1" should work
even if "i" is referenced by a FK.

Regards,Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:
> Is it a problem to allow unique constraints to be deferrable until the
> end of the command though?

Yes.  If you do have a case where this matters, the command updating the
referenced table is most likely different from the one updating the
referencing table, and so the command boundaries aren't the same.

I think what we can do for now is insist that the FK's unique constraint
be immediate-mode.  Trying to relax that looks like a research project
to me.
        regards, tom lane