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

>>>> 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.

Yup yup, I see where you're going.. It's like the third table is a
"name broker" that grants the unique priveledge of using a name in the
database. The other tables will have a FK on it so you'd have to add
that name to the table before it can be inserted elsewhere. The third
table will be unique which ensures a name is only used once.

This would work great, however I'd have to modify a bunch of code to
insert a name into the third table before it could be used.. Since an
admin tool is the only thing that would be doing this (this data
hardly ever changes), this isn't out of the question. I actually
don't need any TRIGGERS if I do this, I just need to modify some code.
This design will ensure my data is always in a valid state.

A fine approach. Thanks!

Mike