Subject Unique foreign key for child tables
Author Rick Debay
I have a parent table with multiple child tables.
Each child row has a foreign key that points to one row in the parent.
Each parent row must have a child row pointing to it.
Each parent row can have only child from any of the child tables
pointing to it.
The child is created before the parent.

A unique key in each child table would prevent two rows from one table
from pointing to the same parent row, but that would duplicate the FK
index. I don't know if that will cause Firebird problems.

I don't know how to enforce the rule that the parent must have a child,
as the FK can't be populated until the parent exists, and the parent
isn't valid unless a child is pointing to it. Right now we just ignore
that requirement, and so far no one has inserted a childless parent

We've also held back on the third requirement of each parent having only
one child, but it looks like each child table would have to manage that
rather than the parent (which would be preferable).

Disclaimer: This message (including attachments) is confidential and may be privileged. If you have received it by mistake please notify the sender by return e-mail and delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is strictly prohibited. Please note that e-mails are susceptible to change. RxStrategies, Inc. shall not be liable for the improper or incomplete transmission of the information contained in this communication or for any delay in its receipt or damage to your system. RxStrategies, Inc. does not guarantee that the integrity of this communication has been maintained nor that this communication is free from viruses, interceptions or interference.