Re: Foreign "Key" confusion

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: 15 Sep 2006 06:14:22 -0700
Message-ID: <1158326062.924705.64880@h48g2000cwc.googlegroups.com>

Martin T. schreef:

> 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
> :-)
>

I was not clear enough, I suppose.
What you have is:

1,1
1,2
1,3
2,1
2,3 for text_id/Lang_id.

What I proposed was:

1,1,1
2,1,2
3,1,3
4,2,1
5,2,3 as ID, text_id/lang_id, where ID would be generated by a

sequence.
Then FK to ID, not to lang_id/text_id.

Or go the fully denormalized way, as Vladimir suggested. Received on Fri Sep 15 2006 - 08:14:22 CDT