Re: Implementing complicated constraints

In article <cjbb40$b0d$1_at_news.caesar.elte.hu>, Agoston Bejo wrote:
> 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?

I am not sure if I understood 100% what you need, but if I did, ATXT belongs to table B, not to A.

Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch/