Re: Foreign "Key" confusion

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: 15 Sep 2006 01:03:22 -0700
Message-ID: <1158307402.403009.69990@m73g2000cwd.googlegroups.com>

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

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. Received on Fri Sep 15 2006 - 03:03:22 CDT