I wouldn't like to comment on whether or not Simon has correctly interpreted the words of the SQL standards committee, because standards committees sometimes word things in an intentionally ambiguous way to placate different interests, and because it seems fairly inconsequential in this case. IMHO this is a useful feature that should be pursued.
There is another precedent that no one mentioned - DB2. From their docs: "You can add a foreign key with the NOT ENFORCED option to create an informational referential constraint. This action does not leave the table space in CHECK-pending status, and you do not need to execute CHECK DATA." I understand that DB2's informational referential constraints won't ever be enforced (they just show intent, which is useful to their planner), so this isn't really the same thing. However, DB2 apparently doesn't initially enforce referential integrity when an FK is created on a table with existing data, without any special syntax on the CREATE: "DB2 does not validate the data when you add the foreign key. Instead, if the table is populated....the table space that contains the table is placed in CHECK-pending status, just as if it had been loaded with ENFORCE NO. In this case, you need to execute the CHECK DATA utility to clear the CHECK-pending status." If I am not mistaken, this is almost exactly the behaviour described by Simon, because referential integrity is, presumably, enforced after the FK is created, but before the CHECK DATA utility is optionally run to ensure that we actually have referential integrity at a later time. I believe that Simon's proposal is essentially sound. I don't know why CHECK DATA operates at the tablespace granularity rather than the FK granularity - IANADB2U. If we followed this behaviour, we wouldn't "let people just arbitrarily claim" that a referential condition exists - rather, we'd let them assert that it /ought/ to exist, and that it will be maintained going forward, and give them the option of verifying that assertion at a later time, after which it actually exists. Unfortunately, this refinement of Simon's proposal would probably entail adding an additional column to pg_constraint. -- Regards, Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
