Re: Does dropping a column from a table mess up foreign keys?

On Fri, 20 Jul 2001, IRWIN,KEITH (Non-HP-Corvallis,ex1) wrote:

> Hi--
>
> I'm getting the following error:
>
>     ERROR:  Relation "accounts" with OID 72496 no longer exists
>
> What I did was to drop a couple of columns using the example Bruce
> provides in his book on page 264.  Briefly, it's something like:
>
>     create table temp as select * from accounts;
>     drop table accounts;
>     create table accounts (etc with columns missing);
>     insert into accounts select <all except dropped cols> from temp;
>     drop table temp;
>     grant update,select,insert,delete on accounts to user;

If you've dumped and restored with 7.0's pg_dump (I'm not sure when it was
fixed, may have been in 7.1.2) there was a problem with the dumped trigger
statements which caused the relationship that tells when to drop the
triggers for fk to not exist after the restore.  Theoretically, your
constraints should have gone away at the drop table accounts;.  I.e.,
even in the best case, the above will not preserve foreign key constraints
pointing to the changed table, you'd need to use alter table to re-add
the constraints.

> And so on.  I've also updated an "accounts_view" based on this table so
> that the dropped columns wouldn't appear (the view being defined with an
> asterisk field list).
>
> I have lots of tables with "references accounts(id)" in them.  Are these
> going to be screwed up because of the drop/create above?
>
> I even tried dumping the DB after the changes, then pg_restoring them, but
> I get a message something like, Relation ACCOUNTS doesn't exist.  I was
> thinking that restoring the db in this way would recalculate the OIDs.

When do you get the relation ACCOUNTS doesn't exist message?  When you try
to do an insert/update?

I'd suggest starting by looking pg_trigger and dropping the constraint
triggers (warning, you need to double quote the constraint name, the
case is significant) that reference accounts and use alter table add
constraint to add the constraints back.