On 16 Jun 2011, at 20:47, Mike Christensen wrote: >>> I know I can setup a FK constraint to make sure Table1.ColA exists in >>> Table2.Key, however what if I want to do the reverse? >>> >>> I want to ensure Table1.ColA does NOT exist in Table2.Key.. Can I do >>> this with any sort of CHECK constraint, trigger, custom function, etc? >>> Thanks! >> >> >> Perhaps it's possible to use a unique constraint in a third table to guarantee those foreign keys can never have the same value. That would probably be more efficient than executing stored procedure code. > > You'd still have to use a TRIGGER to insert any new or updated values > into the third table. Otherwise, you'd have to modify a bunch of code > to insert/update the keys into the third table and that somewhat goes > against the whole idea of making the database responsible for its own > integrity in the first place. No you don't. If Table1.ColA is an FK to Table3.ColA and Table2.ColA is also an FK to Table3.ColA, you can put a unique constraint on Table3.ColA to make sure the values are unique: Table1 Table3 Table2 ------ ------ ------ ColA >-------|- ColA -|-------< ColA If you insert a value in either Table1 or Table2, it first HAS to exist in Table3, due to the FK constraints. However, that still allows for values that are in both tables 1 and 3, just pointing to the same value in Table3. To solve that you add an extra column to all tables, for example: ALTER TABLE Table1 ADD src CHAR(1) DEFAULT 'A'; ALTER TABLE Table2 ADD src CHAR(1) DEFAULT 'B'; And you change the FK constraints in A and B to include "src": Table1 Table3 Table2 ------ ------ ------ ColA >-------|- ColA -|-------< ColA src >-/ \-|- src -|-/ \-< src You also add back a UNIQUE constraint over Table3.ColA (without the "src" column). Now, if you add a value to Table1, it requires a value of (ColA, 'A') in Table3. If you add one to Table2, it requires a value of (ColA, 'B'). If either of those already exist though, you violate the UNIQUE constraint on Table3.ColA. It's probably convenient to write some triggers to auto-generate the records in Table3, but those triggers are NOT needed for relational integrity - they just make the task easier. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4dfaf78612091994554093! -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
