Re: Implementing complicated constraints

"Agoston Bejo" <gusz1_at_freemail.hu> wrote in message news:cjbb40$b0d$1_at_news.caesar.elte.hu...
| Hi, I'm having trouble with implementing some constraints on the database
| level. An example:
|
| --Table A(AID, BID ref. B.BID, ATXT)
| --Table B(BID, CID ref. C.CID)
| --Table C(CID)
|
| upon insertion into or updating in A I would like to force that ATXT is
| unique with respect to CID, i.e.
|
| SELECT COUNT(*) FROM A,B,C
| WHERE A.BID = B.BID
| AND B.CID = CID
| AND CID = fn_get_cid_for_bid(:new.BID)
| AND A.ATXT = :new.ATXT
| AND A.AID <> :new.AID
|
| should be 0.
|
| This I cannot force with a check constraint since it is not allowed to
| contain subqueries.
| If I try to write a trigger that checks the above condition and raises an
| application error, then I always stumble upon the "table is mutating,
| trigger/function may not see it" -type error, since it involves a select
on
| the table that is being changed at the time.
|
| However, it would be better to implement such a constraint on the database
| level, rather than scatter checks throughout the application.
|
| Is there a standard way solve this type of problem?
|
|
|

hopefully i'm understanding the problem correctly....

there's no standard way, since your uniqueness constraint is based on multiple tables (the way you have it currently designed)

however, if A.TXT is NOT NULL, then you could denormalize the table implementation and include CID in A and put a unique constraint on (A.CID, A.TXT). if A.TXT is NULL-able, you may want to denormalize the table implementation and have a table that includes just the PK of A (AID?), CID, and TXT, storing actual TXT entries out-of-line from table A, so that a unique constraint can be put on CID and TXT in that table. the reason NULL/NOT NULL comes into play is that oracle would not prevent multiple NULL values in a one-column unique index, but would prevent multiple TXT nulls in this case, since the CID value would be repeated

++ mcs Received on Tue Sep 28 2004 - 13:25:14 CEST