Re: Implementing complicated constraints

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?

One possibility that I have been experimenting with is to create a Materialized View with a check constraint. In your case it would be something like this:

create materialized view mv1
build immediate
refresh complete on commit as
select b.cid, a.atxt, count(*) cnt
from a, b
where a.bid = b.bid
group by b.cid, a.atxt;

alter table mv1
add constraint mv1_chk
check (cnt = 1)
deferrable;

This works, though the performance of the complete refreshes may be an issue. Received on Tue Sep 28 2004 - 06:15:17 CDT