Re: primary/foreigner keys

On 21 Nov 2001, Luis Miguel Campos wrote:

> Hi,
> I am trying to do the following:
> CREATE TABLE Customer (
> customer_id SERIAL PRIMARY KEY;
> account_id INTEGER UNIQUE REFERENCES Account DEFERRABLE);
>
> CREATE TABLE Account (
> account_id SERIAL PRIMARY KEY,
> customer_leader_id INTEGER UNIQUE REFERENCES Customer DEFERRABLE);
>
> Description:
> I am trying to create two tables where several customers can belong to
> an account and each account has a customer leader.
> This involves having each tables primary keys to be the foreigner key
> in the other table. (other solutions are welcome)
>
> Problem:
> First I can not create either table because the other is not known.
> I tried even within a BEGIN; table creation; commit;

Yep, you need to create the first one without the constraint, then
the second with its constraint and use ALTER TABLE ADD CONSTRAINT
to add the constraint to the first table.

> Secondly If I succeed how do I INSERT values into the tables?

I don't think deferrable implies initially deferred, so you probably
want to add initially deferred to the constraint.  Otherwise the
constraint runs immediate mode unless you explicitly defer it which
is probably not what you're looking for.  Then you need to
make sure the inserts are wrapped in a transaction.  The constraint
should only be checked at commit time.