Message-ID: <42C33C13.5010308@cshl.edu>
Date: Wed, 29 Jun 2005 20:25:55 -0400
From: "Vsevolod (Simon) Ilyushchenko" <simonf@cshl.edu>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.5) Gecko/20041217
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Foreign key pg_dump issue and serial column type
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Hi,

As a relative newbie to postgres, I've run into to weirdisms that I 
don't quite know how to handle:

1. I have a many-to-many table 'people_roles' containing fields 
'person_code' and 'role_code'. It links tables 'people' and 'roles'. 
There are foreign key constraints:

ALTER TABLE PEOPLE_ROLES add CONSTRAINT PRO_PEE_FK FOREIGN 
KEY(PERSON_CODE) REFERENCES PEOPLE(PERSON_CODE) ON DELETE CASCADE NOT 
DEFERRABLE INITIALLY IMMEDIATE;
ALTER TABLE PEOPLE_ROLES add CONSTRAINT PRO_ROE_FK FOREIGN 
KEY(ROLE_CODE) REFERENCES ROLES(ROLE_CODE) ON DELETE CASCADE NOT 
DEFERRABLE INITIALLY IMMEDIATE;

However, when I pg_dump the database and import it on another server, 
the tables are exported alphabetically, so when the 'people_roles' table 
is created with its foreign keys, the table 'roles' does not exist yet. 
Thus, the foreign key creation fails. Is there a way around it?

2. I've just discovered the 'serial' column type and tried to do this:
alter table people_roles alter column  people_roles_code type serial;

To my surprise, it fails:
ERROR:  type "serial" does not exist

However, I can create new tables with the 'serial' type without a hitch. 
Is it a bug or a feature?

I'm running postgres 8.0.3 on Fedora Core 4.

Thanks,
Simon
-- 

Simon (Vsevolod ILyushchenko)   simonf@cshl.edu
				http://www.simonf.com

Terrorism is a tactic and so to declare war on terrorism
is equivalent to Roosevelt's declaring war on blitzkrieg.

Zbigniew Brzezinski, U.S. national security advisor, 1977-81