Change primary key in PostgreSQL table
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_idUPDATE 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 idALTER 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 keyALTER 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_idALTER 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 itDROP SEQUENCE users_id_seq
You can even drop the original id
column if you want to.
I hope it will help someone.