Re: Foreign "Key" confusion

Martin T. wrote:
> 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?
>
> Example:
> --------------
> Parent table "texts":
> columns = (TEXT_ID | LANGUAGE_ID | TEXT)
> pk = TEXT_ID&LANGUAGE_ID
>
> referencing, "child" table:
> columns = (TEXT_ID | EVENT_ID | ...)
> pk = EVENT_ID
> contraint = "the text_id must be a text_id existing in the "texts"
> table"
>
>
> Thanks for any tips on how to do this or any pointers to resources
> where such problems are discussed!
>

After a seemingly much needed coffee, one solution comes to mind using triggers.
That is, handling the dependency btw. the tables in the childs Insert&Update / parents Update&Delete trigger.

This would work, however, I'm wondering if there is a more elegant solution?

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