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" <rde...@accessrxs.com> > 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
