Re: Foreign "Key" confusion

Martin T. wrote:
> 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

Initially, function-based indexes sprang to mind, but you can't have them on aggregate or windowing functions, and there's no other way I know to remove duplicates, so FBIs are not an option. Then I thought that the model doesn't seem to be normalized enough: if you have the same text translated in different languages, then (text_id, language_id) is not the correct primary key. You need to create a table of texts, with primary key of text_id, and a table of translations with foreign key to texts on text_id and, if needed, a unique constraint on (text_id, language_id). Then you can create a table of events and reference the same table of texts with a foreign key on text_id. This seems like the most proper and easiest solution.

Regards,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Fri Sep 15 2006 - 03:25:20 CDT