Re: CREATE TABLE with REFERENCE

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: "kay-uwe(dot)genz" <kug1977(at)web(dot)de>
Cc: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: CREATE TABLE with REFERENCE
Date: 2003-07-28 18:01:01
Message-ID: 20030728105725.X50607-100000@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, 28 Jul 2003, kay-uwe.genz wrote:

> i've a little problem with two tables and FOREIGN KEYs. I've read about
> this long time ago, but didn't remember me where. Well, I hope you can
> help me.
>
> I've create two TABLEs "counties" and "cities". "Countries" have a row
> "capital" is REFERENCEd "cities". "cities" have a row country
> REFERENCEd "countries", where a save the country the city is placed.
>
> And now PG couldn't create the TABLEs, because the referenced table
> doesn't exists in time of creation. Is there another method of creating
> than the ALTER TABLE the first table after the second is living?

Not really. That's the correct way to make the constraints.

> Second question. Is there a method of INSERT INTO both tables VALUES
> without group them in the same Transaction?

You mean insert a row in each table that acts as the pk row for the other?

You could fake it by inserting one in with a NULL for the fk column
(unless they're both NOT NULL), inserting the other and then updating the
first. Otherwise I think you need to be running in a single transaction
(although they could be grouped inside a function or as a trigger for
example).

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Rajesh Kumar Mallah 2003-07-28 18:23:20 Re: CREATE TABLE with REFERENCE
Previous Message Tom Lane 2003-07-28 17:50:22 Re: Can't Build 7.3.4 on OS X