CODEWITHSUNDEEP
sqlpostgresqlprimary-keypostgresql-9.1composite-primary-key
ADTC
ADTC

Reputation: 10096

Change composite primary key column order, avoid exposing table without primary key

Short of dropping the primary key index and re-creating it, how can I alter a composite primary key on a table?

I would love to be able to change the primary key column order and have it re-index and apply immediately in a single statement (so that there is no "downtime" for the primary key).

If it is not possible to do it in a single statement, what is the best way to do it in a single transaction such that the table is not exposed to access for any period of time without a primary key in place?

For example, take a table that has primary key as follows:

PRIMARY KEY (col1, col2, col3, col4)

I want to change the order to (but not add or remove columns):

PRIMARY KEY (col4, col1, col2, col3)

Upvotes: 5

Views: 2050

Answers (1)

Patrick
Patrick

Reputation: 32264

There is no way to do this in a single statement. You will need a transaction block to do this:

-- Create a new index for the new primary key.
CREATE UNIQUE INDEX new_index_name ON table_name (col4, col1, col2, col3);

-- Now enter a transaction block to replace the primary with the new one.
BEGIN;
ALTER TABLE table_name DROP CONSTRAINT pkey_name; -- old index is automatically dropped with the constraint
ALTER TABLE table_name ADD CONSTRAINT constr_name PRIMARY KEY USING INDEX new_index_name;
COMMIT;

There is some "downtime" while replacing the primary key but this is relatively small. The hard work is done in creating the new index but this can be done outside of the transaction block. Any problems are caught when building the index, so before the transaction block is entered and your "downtime" starts. When the index is valid, replacing the primary key is quick.

Upvotes: 9

Related Questions