Re: CREATE TABLE with REFERENCE

kay-uwe.genz wrote:

> Hi @ all,
>
> 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?

Its given in documents though.

ALTER TABLE countries ADD CONSTRAINT "refer_city" FOREIGN KEY (capital)
REFERENCES
cities (city) UPDATE CASCADE ;

mind that city must be pkey in cities for it to work.

similarly the other table can be done.

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

Hmm not sure but it could be interesting to experiment..

>
>
> regards
>
> Kay-Uwe