Re: inverted foreign key constraint

>>
>> I am trying to define a constraint in order to exclude something , in
>> this case an articlenumber, which is stored in one table, also to
 exist
>> in an other table. Something like an inverted foreign key, I would
 say.
>>
>> I tried it with a 'check' constraint, but that didn't allow a subquery
>> on the other table.
>>

No, this won't work, because the database has to know when to evaluate the check constraint. If you have a check constraint on table x which relies on data in table y, then the database will have to check table x's check constraint for *all* records in x when an insert into, update or delete from is issued on table y. This is (close to) impracticable. Also, you are not allowed to use sysdate in check constraints, because the database would then have to evaluate all check constraints using sysdate every time sysdate changes, i.e. every second.

The only allowable check constraints are those whose predicate can change truth value only when a dml is issued against the "owning" table.

>
>The only way that I know of to do this is in a trigger that checks for
>the value in the other table. This may or may not be possible/reliable
>depending on how static your data is.
>

As you say, this is not reliable, because there may be two non-commited transactions trying to insert articles with the same article number. Consider:

1. Trans. A inserts article# 1 into table x
2. The trigger asserts that no articles with article# 1 exists in table y: OK
3. Trans. B inserts article# 1 into table y
4. The trigger asserts that no articles with article# 1 exists in table x: OK, because trans. A hasn't committed yet
5. Trans A and B commits (in whatever sequence)

Trigger-enforced unique keys won't even be reliable for one single table.

If you serialize the transactions, using locks or semaphores, or whatever means, you might be safe. However, you will have to make sure that the lock/semaphore is released after commit. Also, you'll severely degrade the database throughput.

The solution:

Since both tables contain an article# column, merge these tables into one new table and create a primary or unique key on article#. Then, create two views with columns corresponding to the columns of your two tables. You may consider creating the views "with check option" if you have an "article type" column in your new table.

This solution will solve your particular problem, since primary/unique keys are evaluated at the time of the insert/update, even if the inserted/updated record is not visible to other transactions yet.

--
Roy Brokvam

Hitchhikers against spam! Correct my last name to e-mail me.