Re: Constraint to ensure value does NOT exist in another table?

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!