Thanks, we'll be implementing this. Hopefully Firebird will get
deferred constraints and I'll be able to tackle the other half of our
problem.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of unordained
Sent: Monday, May 14, 2012 11:25 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Unique foreign key for child tables

---------- Original Message -----------
From: "Rick Debay" <rdebay@...>

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

------- End of Original Message -------

Using a constant tag to ensure a parent has children only in one child
table at a time:

alter table parent add child_type char(1) check (value in ('A', 'B',
'C')); alter table child_a add child_type char(1) check (value = 'A')
default 'A';
/* repeat */
alter table child_a add constraint fk_a foreign key (parent_id,
child_type) references parent (id, child_type) on update cascade on
delete set null;
/* repeat */

You already have example triggers for ensuring the parent gets created
when the child is created, preventing duplicates via the insert
statement; but if you want to allow parentless children, that doesn't
seem quite right. You'd have to create the child, then re-delete the
parent? What about updating a child's FK to the parent, such that it
points to another pre-existing parent (with children in another table)?

No childless parents:

create global temporary table altered_parents ( parent_id integer not
null primary key );

create trigger track_parents after insert or update or delete on parent
as begin if (inserting or updating) then
update or insert into altered_parents (parent_id) values (new.id); if
(deleting or updating) then
update or insert into altered_parents (parent_id) values (old.id); end

create trigger track_parents after insert or update or delete on child_a
as begin if (inserting or updating) then
update or insert into altered_parents (parent_id) values
(new.parent_id); if (deleting or updating) then
update or insert into altered_parents (parent_id) values
(old.parent_id); end
/* repeat */

create exception childless_parent 'You are not allowed to create a
childless parent'; create trigger check_parents on transaction commit as
begin if (exists(select * from altered_parents inner join parent on
parent.id = altered_parents.parent_id /* once a parent is deleted, we no
longer care to check it */ where not exists (select * from child_a where
child_a.parent_id =
altered_parents.parent_id) and not exists (...) and not exists(...)))
/* you could use the same technique to catch multi-children parents,
here, without 'tagging' the rows as above */
exception childless_parent;
delete from altered_parents; /* I normally put the body of these
triggers in a procedure I can call 'as i go', with a final double-check
at transaction commit; in that case, you only want to re-check what
hasn't been previously checked. */ end

--Philip


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the
main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links