Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Foreign "Key" confusion

Re: Foreign "Key" confusion

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 15 Sep 2006 01:11:55 -0700
Message-ID: <1158307915.564208.115490@e3g2000cwe.googlegroups.com>


Frank van Bortel wrote:
> Martin T. schreef:
>
> > Hi all.
> > (Oracle 9.2.0.1.0, Windows XP)
> >
> > I was wondering how I could restrict the values of a column in a child
> > table to a parent column when that parent column is NOT unique.
> > I know a foreign key can only be used to reference a unique (set of)
> > columns.
> >
> > What could I do to enforce child <-> parent relationship for a
> > non-unique parent column?
>
> not by using foreign keys.
> you could by using triggers: fire a trigger on insert of child
> to chech whether a parent text_id exists (use the where exists
> construction).
>

Frank - thank you .. had the same though at the same time it seems :)

> However, I would change your model - use a single column (sequence?)
> primary key, and a unique, compount key on LNG_ID and TXT_ID,
> if TXT_ID is meaningful at all.
> Use a proper foreign key on child to reference the (meaningless) PK
> on your parent table.

Well, problem is, I do not want to reference a unique text, I want to reference a text that exists in multiple languages. (hence the compound key txt_id & lng_id).
(Of course the layout of the text table may be subject to discussion :-)

cheers,
Martin Received on Fri Sep 15 2006 - 03:11:55 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US