Re: Quasi Foreign Key

Your case is not a quasi foreign key at all.

Expressed in terms of the underlying business rules, it amounts to saying that
some relationships are not mandatory. It happens all the time.

Example: A department need not have a manger, but, if it does have one, the manager must be an employee.

As far as implementing a non mandatory foreign key goes, this is database product dependent. Depending on the relational DBMS product you are using,  this ranges from the trivially simple to the damn near impossible.

If worst comes to worst, implement the relationship as if it were a many-to-many relationship. This means constructing a separate table, with foreign keys back both of the original tables.

Then, if you have a department with no manager, just don't insert any rows for that department into the department-manager table!

I know of no product that will forbid omitting an entire row!

As Yogi Berra used to say, "if people are going to stay away from the ball park, there isn't any way you can stop them!"

Steve Long wrote in message <8tmqrt$bl0$1_at_bob.news.rcn.net>...
>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?
>
>
Received on Sun Nov 05 2000 - 09:16:03 CST