From: | Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)nl> |
---|---|
To: | Mike Christensen <mike(at)kitchenpc(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Constraint to ensure value does NOT exist in another table? |
Date: | 2011-06-17 06:42:59 |
Message-ID: | F703F446-719A-4C0D-BC53-164DEF60F0B1@solfertje.student.utwente.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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!
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2011-06-17 06:46:50 | Re: PostgreSQL 8.4.8 bringing my website down every evening |
Previous Message | Achilleas Mantzios | 2011-06-17 05:43:29 | Re: PostgreSQL 9.0 or 9.1 ? |