Re: Referential Integrity w/XOR
Date: 11 Jun 1994 00:23:01 GMT
Message-ID: <rayo.104.0_at_ontko.com>
In article <1994Jun9.180817.8189_at_bme.ri.ccf.org> mtuason_at_bio.ri.ccf.org (Mike Tuason) writes:
>From: mtuason_at_bio.ri.ccf.org (Mike Tuason)
>Subject: Referential Integrity w/XOR
>Date: Thu, 9 Jun 1994 18:08:17 GMT
>
>I have a problem with referential integrity constraints that
>I'm not sure how to implement. I have three entities in
>which two are related to one common entity such as sales.
>The foreign key in the 'sales' table must reference a 'part'
>or 'service' but not both for a single row.
[snip]
>There's no problem in defining a constraint for one relationship,
>but implemeting an XOR where a 'sales' item can be for either a
>'service' or 'part' I can't find in the docs. Oracle tech support
>is clueless at the moment. I thought about using database triggers,
>but doing that would require preventing the insertion of a row
>in the 'sales' table which I can't find how to do.
If you're using the foreign key constraints to 'document' the relationship between both tables, go ahead and create the constraints but DISABLE them.
If you would prefer to use constraints, you can create another entity SALES_ITEM which is parent (ONE to ZERO_OR_ONE) with PART, and also parent ( ONE TO ZERO_OR_ONE) with SERVICE, and also parent (ONE TO ZERO_OR_MANY) to SALES. If you're stuck with your existing design, you can use database triggers. It would be something like BEFORE INSERT OR UPDATE ON SALES FOR EACH ROW and use the packaged procedure raise_application_error to cause the trigger to fail if the corresponding PART or SERVICE doesn't exist.
Ray
Ray Ontko rayo_at_ontko.com "Ask me about Database Illustrator(tm)" Ray Ontko & Co info_at_ontko.com ftp cscns.com:/pub/ontko Received on Sat Jun 11 1994 - 02:23:01 CEST