pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
primary/foreigner keys
2+ messages / 2 participants
[nested] [flat]

* primary/foreigner keys
@ 2001-11-22 00:11 Luis Miguel Campos <lcampos@ics.uci.edu>
  2001-11-23 18:38 ` Re: primary/foreigner keys Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 1 reply; 2+ messages in thread

From: lcampos@ics.uci.edu @ 2001-11-22 00:11 UTC (permalink / raw)
  To: pgsql-sql

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;
Secondly If I succeed how do I INSERT values into the tables?

I am sure someone has already faced such a problem, so there must be a
solution out there but I could not find it in the postgresql
documention.

Thank you for your help,
Miguel



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: primary/foreigner keys
  2001-11-22 00:11 primary/foreigner keys Luis Miguel Campos <lcampos@ics.uci.edu>
@ 2001-11-23 18:38 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Stephan Szabo @ 2001-11-23 18:38 UTC (permalink / raw)
  To: Luis Miguel Campos <lcampos@ics.uci.edu>; +Cc: pgsql-sql

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.





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-11-23 18:38 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-22 00:11 primary/foreigner keys Luis Miguel Campos <lcampos@ics.uci.edu>
2001-11-23 18:38 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

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