Re: Foriegn Key Question

Wally wrote:
> All,
>
> Database Version: 10.2.0.1
> OS: XP Pro
>
> I have a question about Foriegn Keys. Forgive me if this has been
> answered before.
>
> I have three tables which are
>
> subject with primary_key subject_id (VARCHAR2(10)),
> contact with primary_key contact_id (VARCHAR2(10)),
> &
> address with primary_key address_id (VARCHAR2(10))
>
> The address table has another column called owner_id (VARCHAR2(10))
> where the owner_id could be the subject_id or the contact_id, because
> either the subject or the contact could have an address. Currently the
> constraint is being maintained through pl/sql code.
>
> I know that I can have one foriegn key where the address.owner_id is
> linked to the subject.subject_id, or one foriegn key where the
> address.owner_id is linked to the contact.contact_id, but not both.
> Please correct me if I am wrong.
>
> This will not work since the owner_id can be either the subject_id or
> the contact_id.
>
> Is there a way other than a database trigger or pl/sql code where I can
> define the above relationship in the database.
>
> Thanks for your help.
>

You'll need to introduce what I call a crosswalk table. You'll need two more tables, one between ADDRESS and CONTACT and another between ADDRESS and SUBJECT. These new tables will only contain two columns, the id's of the tables they are joining. For instance, create these two tables:

CREATE TABLE contact_addresses (

    contact_id VARCHAR2(10),
    address_id VARCHAR2(10));

CREATE TABLE subject_addresses (

    subject_id VARCHAR2(10),
    address_id VARCHAR2(10));

When you insert an address into ADDRESS, and that address belongs to a SUBJECT, then not the id of the address and the subject. Insert those id's into the SUBJECT_ADDRESS table. The SUBJECT_ADDRESS table becomes a crosswalk between the subject and it's address. A simple join of all three tables involved will get all of the information.

HTH,Brian

-- 
===================================================================

Brian Peasland
dba_at_nospam.peasland.net
http://www.peasland.net

Remove the "nospam." from the email address to email me.


"I can give it to you cheap, quick, and good.
Now pick two out of the three" - Unknown