Re: Implementing complicated constraints

Brian Dick wrote:
> After some consideration (and a beer) I like the following better. If
I had
> a chance to study the business domain, I would probably come up with
> something different, yet.
>
> Table A(AID, BID ref. B.BID, TID ref T.TID)
> Table B(BID, CID ref. C.CID)
> Table C(CID)
> Table T(TID, CID ref C.CID, ATXT) with unique constraint on CID, ATXT

Yes, that works. However, maybe now we need a constraint to ensure that the CID in T is consistent with the CID in the B row associated with the A row associated with T, if you see what I mean. That rule was implied by the original design.

In other words:

NOT EXISTS
( SELECT NULL
FROM A, B, T

WHERE A.BID = B.BID
AND   A.TID = T.TID
AND   B.CID != T.CID

This is another constraint that cannot be expressed using Oracle constraints alone, though some of the ideas we have discussed earlier might work. For example:

create materialized view mv
build immediate
refresh complete on commit as
select 1 dummy
from a, b, t

where a.bid = b.bid
and   a.tid = t.tid
and   b.cid != t.cid;

alter table mv add constraint mv_chk
check (1=0); Received on Thu Sep 30 2004 - 05:09:12 CDT