What happens to the index of a primary key after a DROP CONSTRAINT?

Question

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")

Answer 1

Disclaimer

This is experimental and only tested rudimentarily. Proceed at your own risk. I would not use it myself and just drop / recreate constraints with standard DDL commands. If you break entries in catalog tables you could easily mess up your database.

For all I know, there are only two differences between a PRIMARY KEY and a UNIQUE constraint in the catalog tables (the index itself is identical):

pg_index.indisprimary:
PRIMARY KEY constraint ... true
UNIQUE constraint ... false

pg_constraint.contype:
PRIMARY KEY constraint ... 'p'
UNIQUE constraint ... 'u'

You could convert constraint and index in place, from PRIMARY KEY constraint to UNIQUE constraint, my_idx being the (optionally schema-qualified) index name:

UPDATE pg_index SET indisprimary = false WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'u' WHERE conindid = 'my_idx'::regclass;

Or upgrade from UNIQUE to PRIMARY KEY:

UPDATE pg_index SET indisprimary = true WHERE indexrelid = 'my_idx'::regclass;
UPDATE pg_constraint SET contype = 'p' WHERE conindid = 'my_idx'::regclass;

Of course, there can only be a single PK per table.

Answer 2

Yes, dropping the primary key constraint will drop the index.

But it should be noted that serial is not a real type, it's a shortcut for INT default nextval('some_sequence'), where some_sequence is auto-generated.

You don't say what is the current column type, but since you expected to be able to reuse the index for the serial column, let's assume it's INT.

It's not obvious in the question if you plan to ultimately drop the old column, but if you just want to transform it into a serial column, dropping and recreating the associated constraints is not necessary. Just create a sequence, initialize it to the next value of the primary key, and let its nextval be the default value of the column, as in:

CREATE SEQUENCE myseq START WITH 12345;

where 12345 is replaced by the current max(pk)

And then:

ALTER TABLE tablename ALTER COLUMN pk_col SET DEFAULT nextval('myseq');