Re: inverted foreign key constraint

Thanx!!

You're absolutely right, this is problably the best solution.Smooth and safe. Because this is purely an entity-related problem I did give this some thought, and I guess I'll just give it a go. But thanx anyway!!

B.t.w., I asked around a little about this particular problem, and was told that in some DBMS's it is possible to use a subquery in a constraint definition. But again, not in Oracle.

                Arjen

Roy Brokvam schreef:

> >>
> >> 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.
Received on Fri Jan 19 2001 - 14:22:47 CST