Change primary key in PostgreSQL table

Question

I have users table in my PostgreSQL 9.3.6 database with two columns: id and another_id. The id is a primary key, the another_id is just another integer column with unique constraint.

There are other tables that reference users by primary key.

Here's the users table description:

Table "public.users"         Column        |              Type              |               Modifiers                | Storage | Stats target | Description  ----------------------+--------------------------------+----------------------------------------+---------+--------------+-------------  id                   | integer                        | not null                               | plain   |              |   another_id           | integer                        | not null                               | plain   |              |   Indexes:     "users_pkey" PRIMARY KEY, btree (id)     "uniq_1483a5e93414710b" UNIQUE, btree (another_id)  Referenced by:     TABLE "foo_table" CONSTRAINT "fk_4affc6e5a76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE     TABLE "bar_table" CONSTRAINT "fk_72936b1da76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE     TABLE "baz_table" CONSTRAINT "fk_83adbaf0a76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE 

Here's foo_table description:

Table "public.foo_table"     Column    |              Type              |                   Modifiers                   | Storage  | Stats target | Description  --------------+--------------------------------+-----------------------------------------------+----------+--------------+-------------  id           | integer                        | not null                                      | plain    |              |   user_id      | integer                        |                                               | plain    |              |   Indexes:     "foo_table_pkey" PRIMARY KEY, btree (id)     "idx_e52ffdeea76ed395" btree (user_id) Foreign-key constraints:     "fk_e52ffdeea76ed395" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE 

How do I replace primary key in PostgreSQL table from id column to another_id column and maintain data integrity?

Answer 1

I've spent some time and finally came up with a working solution.

I will publish it here for future reference.

Solution

First of all, you have three tables (foo_table, bar_table, baz_table) which are pointing to your users table by means of foreign keys (called user_id in all cases). You will need to replace the IDs stored in those columns from id to another_id. Here's how you can do it:

-- We are dropping the foreign key constraint on dependant table (in other case it will prevent us from updating the values) ALTER TABLE foo_table DROP CONSTRAINT fk_e52ffdeea76ed395;  -- Then, we're swapping values in foreign key column from id to another_id UPDATE foo_table T SET user_id = (SELECT another_id FROM users WHERE id = T.user_id);  -- And finally we're creating new foreign key constraint pointing to the another_id instead of id ALTER TABLE foo_table ADD CONSTRAINT fk_e52ffdeea76ed395 FOREIGN KEY (user_id) REFERENCES users (another_id) ON DELETE CASCADE; 

You will need to repeat the above queries for each dependent table.

After that, all dependent tables will point to your new another_id column.

In the end we will just need to replace the primary key:

-- 1. Dropping the original primary key ALTER TABLE users DROP CONSTRAINT users_pkey  -- 2. Renaming existing index for another_id (optional) ALTER INDEX uniq_1483a5e93414710b RENAME TO users_pkey  -- 3. Creating new primary key using existing index for another_id ALTER TABLE users ADD PRIMARY KEY USING INDEX users_pkey  -- 4. Creating index for old id column (optional) CREATE UNIQUE INDEX users_id ON users (id)  -- 5. You can drop the original sequence generator if you won't need it DROP SEQUENCE users_id_seq 

You can even drop the original id column if you want to.

I hope it will help someone.