Received: from davinci.ethosmedia.com (davinci.ethosmedia.com [209.10.40.250]) by postgresql.org (Postfix) with ESMTP id 08DF7475451 for ; Thu, 7 Mar 2002 15:30:18 -0500 (EST) Received: from [209.10.40.250] (account ) by davinci.ethosmedia.com (CommuniGate Pro WebUser 3.5.2) with HTTP id 820536; Thu, 07 Mar 2002 12:30:15 -0800 From: "Josh Berkus" Subject: Re: psql question To: Charles Hauser Cc: pgsql-sql@postgresql.org X-Mailer: CommuniGate Pro Web Mailer v.3.5.2 Date: Thu, 07 Mar 2002 12:30:15 -0800 Message-ID: In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 8bit X-Archive-Number: 200203/89 X-Sequence-Number: 6970 CHarles, > When I created TABLE clone I initially defined a column: > > gb_accessions_id INTEGER REFERENCES gb_accessions(gb_accessions_id) > NULL > > I have renamed TABLE gb_accessions to TABLE gb, and its column > gb_accessions_id to gb_id. > > I next renamed the column 'gb_accessions_id' in TABLE clone to > 'gb_id'. > In renaming this column in TABLE clone I appear to have lost the > reference to TABLE gb (see below). > > Questions: > > 1. During which step in my renaming did this occur? > 2. How should I have carried this out? > 3. Can I add back REFERENCES gb(gb_id)? I generally find that it is much easier, over all, to dump the database to a pgdump sql-text file, and do my editing there whenever I need to make significant exits to important database tables. Otherwise, it is difficult to make sure that all of your constraints, rules, triggers, and FKs update correctly. You can add back the REFERENCES constraints. First, check the table definitions of the child tables to see if they still have orphaned constraints pointing to gb_accessions. (do this through psql's \d table_name) If so, drop those constraints. Unfortunately, I do not know the syntax for ALTER CONSTRAINT ... FORIEGN KEY .... Post it as a question to the list. -Josh