Re: foreign keys constraints, depending on each other

> > I was just creating this little database for demonstrating the use of
> > foreign keys constraints.
> >
> > I was about the create 3 tables, namely mother, father and child. Mother has
> > a foreign key pointing at father ( id ), and father has a foreign key
> > pointing at mother ( id ). Child has one pointer to mother ( id ) and one
> > pointer to father ( id ). How can I prevent the error message from occurring?
>
> You don't put the constraint at table creation time.  The table referenced
> by the references has to exist.  Use ALTER TABLE to add the constraint
> after creating table father.
>

I tried:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO mother (fatherID, name) VALUES ( 1, 'mamma' ) ;
INSERT INTO father (motherID, name) VALUES ( 1, 'pappa' ) ;
INSERT INTO child (motherID, fatherID, name) VALUES (1, 1, 'barn 1') ;
INSERT INTO child (motherID, fatherID, name) VALUES (1, 1, 'barn 2') ;
COMMIT;

...which did not work. Still it complains about key referenced from mother not
found in father.

---
Daniel Akerud