Vladimir M. Zakharychev wrote:
> 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.
>
Thanks for taking the time to layout the proper normalized solution!
Makes a lot of sense :-)
cheers,
Martin
Received on Fri Sep 15 2006 - 03:32:56 CDT
