pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone23.bigpanda.com>
To: Luis Miguel Campos <lcampos@ics.uci.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: primary/foreigner keys
Date: Fri, 23 Nov 2001 10:38:33 -0800 (PST)
Message-ID: <20011123103517.I84165-100000@megazone23.bigpanda.com> (raw)
In-Reply-To: <5fa15e5f.0111211611.c4dd97b@posting.google.com>

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.





view thread (2+ messages)

Message-ID: <20011123103517.I84165-100000@megazone23.bigpanda.com>
Permalink:  ../20011123103517.I84165-100000@megazone23.bigpanda.com/
Also on:    postgresql.org/message-id/20011123103517.I84165-100000@megazone23.bigpanda.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: sszabo@megazone23.bigpanda.com, lcampos@ics.uci.edu
  Subject: Re: primary/foreigner keys
  In-Reply-To: <20011123103517.I84165-100000@megazone23.bigpanda.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox