Clever. I'll take a look at it.

> What isn't prevented is addition of parentless children, but you didn't mention that.

Sad, but it's allowed.

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: Saturday, May 05, 2012 5:34 AM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] RE: Unique foreign key for child tables

>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 accidentally.
>
>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).

A few of your requirements are quite simple to solve, Rick.

Let's take an example:

MASTER
PK_M
...

CHILD_A
PK_CA
PK_M
...

CHILD_B
PK_CB
PK_M
...

...

Add another table:

ALL_CHILDREN
PK_M PRIMARY KEY

triggers:

CREATE TRIGGER CHILD_A_AI FOR CHILD_A ACTIVE AFTER INSERT AS BEGIN
INSERT INTO ALL_CHILDREN(PK_M) VALUES (NEW.PK_M); END

CREATE TRIGGER CHILD_B_AI FOR CHILD_B ACTIVE AFTER INSERT AS BEGIN
INSERT INTO ALL_CHILDREN(PK_M) VALUES (NEW.PK_M); END

CREATE TRIGGER CHILD_A_AD FOR CHILD_A ACTIVE AFTER DELETE AS BEGIN
DELETE FROM ALL_CHILDREN AC WHERE AC.PK_M = OLD.PK_M; END

CREATE TRIGGER CHILD_B_AD FOR CHILD_B ACTIVE AFTER DELETE AS BEGIN
DELETE FROM ALL_CHILDREN AC WHERE AC.PK_M = OLD.PK_M; END

and a FOREIGN KEY:

ALTER TABLE MASTER ADD CONSTRAINT FK_ALL_CHILDREN FOREIGN KEY REFERENCES ALL_CHILDREN(PK_M)...

This will prevent the same master from being added to several children (provided they all have the trigger) and prevent a MASTER from being added unless there are children. What isn't prevented is addition of parentless children, but you didn't mention that.

HTH,
Set


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.