Re: Circular references

On 21 June 2013 05:08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Melvin Call <melvincall979(at)gmail(dot)com> writes:
> > I was given a dump of an existing remote schema and database, and the
> > restore on my local system failed. Looking into it, I found a circular
> > parent-child/child-parent relationship, and I don't believe this existing
> > structure is viable. To summarize, the organization entity has an
> attribute
> > of creator, which is a foreign key to the user table, but the user has to
> > belong to an organization, which is a foreign key to the organization
> > table. Since neither are nullable, there is no way to create even an
> > initial record. My guess is one or both of the tables was first
> populated,
> > and then the FK constraint(s) created.
>

That should be possible if you create one of the FK constraints as a
DEFERRED constraint. Which one depepnds on what order you supply the data
in.

And make sure you supply all the data in the circular reference in the same
transaction, or the deferred constraint check will kick in too early.

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.