Re: CREATE TABLE with REFERENCE

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).