FAQ
[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

Search Discussions

  • Dean Rasheed at Jul 28, 2009 at 9:11 pm
    2009/7/28 Tom Lane [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
  • Jeff Davis at Jul 28, 2009 at 9:16 pm

    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
  • Tom Lane at Jul 28, 2009 at 9:40 pm

    Jeff Davis 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJul 28, '09 at 8:09p
activeJul 28, '09 at 9:40p
posts4
users3
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase