>> 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
samevalue. 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.
What I'm ideally looking for here is a way to ensure the DB cannot
possibly exist in this state. Foreign keys let me do that, a trigger
(if written correctly) kinda does too so long as the data started out
in a valid state and the trigger is always run..