Re: foreign keys constraints, depending on each other

On Sun, 10 Jun 2001 zilch(at)home(dot)se wrote:

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

Ah, that's because you didn't define the constraints DEFERRABLE. SET
CONSTRAINTS ALL DEFERRED only changes the state of deferrable constraints.

If you don't specify a time, it's INITIALLY IMMEDIATE. If it's initially
immediate, it's NOT DEFERRABLE unless DEFERRABLE is explicitly given.