Re: Implementing complicated constraints

From: Brian Dick <bdick_at_cox.net>
Date: Thu, 30 Sep 2004 08:28:18 -0400
Message-ID: <1hm8kvcji4qfj.5ruz21ys2wmk$.dlg@40tude.net>

On 30 Sep 2004 03:09:12 -0700, Tony Andrews wrote:

> 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);

Try this (again). This is Mark's solution with an additional table.

Table C(CID,

        pk(CID))

Table B(BID, CID,

        pk(BID, CID),
        fk(CID ref C(CID)))

Table T(CID, ATXT,
        pk(CID, ATXT),
        fk(CID ref C(CID))

Table A(AID, BID, CID, ATXT,
        pk(AID, BID, CID),
        fk(BID, CID) ref B(BID, CID),
        fk(CID, ATXT) ref T(CID, ATXT))

Received on Thu Sep 30 2004 - 07:28:18 CDT