Re: Quasi Foreign Key

David Fitzjarrell wrote:
>
> In article <8tmqrt$bl0$1_at_bob.news.rcn.net>,
> "Steve Long" <steven.long_at_erols.com> wrote:
> > i have the following scenario and could use some assistance.
> >
> > parent table has composite primary key consisting of three columns.
> >
> > child table has a column which has a foreign key to parent table
 using the
> > first column of the parent table's composite primary key. however,
 the
> > child table's column may be null, but if it is not null it's value
 must be
> > present in the parent table's column.
> >
> > the problem is that the value "null" in the child table's column is
 not an
> > allowable value in the parent table's column since in the parent
 table this
> > column is part of the primary key.
> >
> > any suggestions on how to implement this?
> >
> >
>
> You are correct in refering to this as a 'quasi-foreign key' and
> declaring it as a foreign key will not work as there are allowable null
> values in the column. I would suggest a trigger to validate non-null
> values in the 'child' table against the 'parent' table. If the column
> value is null then the trigger would perform no task; if the column
> value is not null the value would be checked against the valid entries
> in the 'parent' table to determine if the value

And then there is the other half of the job: to make sure that changes to the parent table do not invalidate the quasi foreign key in the child table.

> .
> --
> David Fitzjarrell
> Oracle Certified DBA
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Oct 31 2000 - 19:21:26 CST