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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
