I am running PostgreSQL 9.1.4.
I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :
1 - Remove the pkey constraint on the current primary key because it is currently not a serial type.
2 - Add a serial type column and have that new column be the primary key.
3 - Setting the old column of step #1 to UNIQUE and recreate foreign keys for all tables that had a foreign key pointing to my table. In fact, those tables would all have two foreign keys pointing to my table: one towards the old column that is currently the primary key, and another towards the new serial type column
I have successfully created a script that does this, but I wish to know:
a) Does dropping the pkey constraint at step #1 above also drop the index associated to that primary key?
b) If not, is there any way to reuse that index? After adding back the UNIQUE constraint at step #3, would a new index be created or would it use the one that existed before? No content is being changed in the table except for the new column that is created (serial primary key).
EDIT (Some clarifications):
- Let's call the old pk column OLD_PK and the to-be-created-by-my-script new pk column NEW_PK
- The OLD_PK column's type is INT
- The OLD_PK column will not and can not be dropped (its information still has value) therefore it also can not be converted itself to a type serial, the existing data must stay.
- I am aware that serial is a shortcut. Like I said I have come up with a working script, the part a colleague and I weren't sure about is the index and how it would work from that point of view.
- All tables that previously had one foreign key pointing to OLD_PK will, after the execution of the script, have one foreign key pointing to OLD_PK (say "fkey_old_pk") and another foreign key pointing to NEW_PK (say "fkey_new_pk")