Implementing complicated constraints
Date: Tue, 28 Sep 2004 11:28:56 +0200
Message-ID: <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.
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?
Received on Tue Sep 28 2004 - 11:28:56 CEST
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.