Circular foreign key constraints

[navalverma]

-- Problem:
-- I have to have tables for parents and children.
-- Every parent can have multiple children and can possibly have a favorite one
-- Every child has to have exactly one parent
-- Deleting a parent should delete it's children
-- Deleting a favorite child from children should set favorite_cid to null

-- My Solution:

drop table parents;
drop table children;

create table parents (
pid integer not null primary key,
favorite_cid integer,
other_info varchar(10)
);

create table children (
cid integer not null primary key,
pid integer not null,
other_info varchar(10)
);

alter table children add foreign key(pid) references parents(pid) on delete cascade;
alter table parents add foreign key(favorite_cid) references children(cid) on delete set null;

-- I am getting the following error.
alter table parents add foreign key(favorite_cid) references children(cid) on delete set null
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0632N FOREIGN KEY "FAVORITE_CID..." is not valid because the table cannot
be defined as a dependent of table "DB2INST1.CHILDREN" because of delete rule
restrictions (reason code = "2"). SQLSTATE=42915
-- Any suggestions on how I can implement the desired relationships ??

Thanks

 

[BrianTyler]

I don't think that the structure as defined is really correctly normalised, though it does make logical sense.

It may me a suitable candidate for the use of a trigger mechanism to clear the favourite on the parent table.