pg_dump --> restore loses constraints/triggers? - Mailing list pgsql-general

 Greetings,

 We've seem to come into a problem with some of our referential integrity
checks, and I was wondering if anyone else had experienced this.

 To upgrade our DB from 7.1 to 7.3 I did:

 pg_dump --create dbname > backup_file.sql

 Then created users, and \i backup_file.sql from the command prompt.

 All the data seems to be there, but many foreign key constraints seem to
be broken. For example, we created this table:

CREATE TABLE ind (
ind_id INTEGER DEFAULT NEXTVAL(''"ind_id_seq"''::text),
bnr_id INTEGER PRIMARY KEY,
...(other columns)
);

and another table that makes reference to it:

CREATE TABLE grp_join_ind_clt (
grp_id INTEGER REFERENCES grp ON UPDATE CASCADE ON DELETE CASCADE,
bnr_id INTEGER REFERENCES ind ON UPDATE CASCADE ON DELETE CASCADE
);

Now, when we've restored, the first trigger above (on the table 'grp')
works, but not the one on 'ind'. Also, the contstraints were not added as
contstraints to the tables, but as CREATE CONSTRAINT TRIGGER "<unnamed>"
instead. Is this normal? Or should they be ALTER TABLE's?  We're stumped
as to what happened, and to how to fix it. Any ideas/thoughts? Any info on
this would be much appreciated.

 Thanks in advance.

Cheers,

Chris

--

Christopher Murtagh
Webmaster / Sysadmin
Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax:  (514) 398-2017