Re: CREATE TABLE with REFERENCE

Stephan Szabo wrote:

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

But isnt' foreign key constraints deferrable inside transactions?
i vaugely remember doing it  . istn' it the best way of doing it?
[ if at all it works :-) ]

regds
Mallah.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings