Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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")
share|improve this question
add comment

2 Answers

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;
share|improve this answer
    
Useful when you need to do it, but it's good you've noted that system catalog twiddling is dangerous and might trash your database - at least to the point of needing expert repair, if not worse. –  Craig Ringer Nov 17 '12 at 11:47
add comment

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');
share|improve this answer
    
Thank you for your reply, you were right about the not-so-obvious parts of my question. I have edited it and added clarifications. It is not an option to convert the current column used as PK to serial as explained in my edit. –  Martin G. Nov 16 '12 at 20:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.