Does dropping a column from a table mess up foreign keys?
От | IRWIN,KEITH (Non-HP-Corvallis,ex1) |
---|---|
Тема | Does dropping a column from a table mess up foreign keys? |
Дата | |
Msg-id | 8A5088D4530AD511870E00D0B7AF539A402F49@xcor03.cv.hp.com обсуждение исходный текст |
Ответы |
Re: Does dropping a column from a table mess up foreign
keys?
|
Список | pgsql-general |
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; 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. If I use pg_restore with --orig-order, I get a failure saying "Accounts" doesn't exist. If I use pg_restore with --oid-order, I get an error about a sequence that doesn't exist, or something like that. This tells me that pg_dump --format=c is really screwed up, or that I fundamentally don't understand how to backup/restore my database. I used to do: pgdump > dump.file and then cat dump.file | psql database but that no longer works either, as the pgdump dumps the grant statements BEFORE the tables they're supposed to grant permissions to. Is there some command I should do BEFORE the changes due to the fact that other tables have contraints based on "accounts"? (Now that I think about it, I'm amazed that I can drop a table with data dependent on it.) Clues? Keith ---- Keith Irwin Development Ecosystem Team Architecture & Development Services E-Services Deployment & Operations Email: keith_irwin@non.hp.com Phone: 541.715.0032
В списке pgsql-general по дате отправления: