Re: Foriegn Key Question

"Wally" <wallyraju_at_gmail.com> wrote in message news:1155306077.832063.276330_at_m73g2000cwd.googlegroups.com...
> 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.
>

Hi Wally,

The design just needs to be tweaked a little bit here. I guess the obvious question would be how do you determine from the address table whether the owner_id is a subject or a contact and just as importantly how would Oracle determine whether a FK value is valid if it could be ambiguously defined between the two parent tables ?

Some possible solutions could include:

• Defining a separate column in the address table for each FK value (and simply making them nullable values as appropriate)
• Combining the contents of the subject and contact tables into the one object
• Creating a separate summary table holding just the PK values and table type of the subject and contact tables and creating just the one FK column in the address table to this table

I would recommend some such design modification.

Cheers

Richard Received on Fri Aug 11 2006 - 09:51:07 CDT