Re: Foreign "Key" confusion

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