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

database-designindexpostgresqlprimary-keyunique-constraint

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

Best Answer

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 the 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:
For PRIMARY KEY constraint ... TRUE
For 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;
Related Question