scaryguy/change_primary_key.md

Last active October 6, 2025 15:35

Star (224) You must be signed in to star a gist
Fork (30) You must be signed in to fork a gist

Select an option

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/scaryguy/6269293.js"></script>
Save scaryguy/6269293 to your computer and use it in GitHub Desktop.

How to change PRIMARY KEY of an existing PostgreSQL table?

Raw

-- Firstly, remove PRIMARY KEY attribute of former PRIMARY KEY
ALTER TABLE <table_name> DROP CONSTRAINT <table_name>_pkey;

-- Then change column name of  your PRIMARY KEY and PRIMARY KEY candidates properly.
ALTER TABLE <table_name> RENAME COLUMN <primary_key_candidate> TO id;

-- Lastly set your new PRIMARY KEY
ALTER TABLE <table_name> ADD PRIMARY KEY (id);

grantrobertsmith commented May 11, 2017

It's important to note that if the primary key is used as a foreign key constraint in other tables, you'll have to include the keyword CASCADE at the end of the DROP CONSTRAINT command. Subsequently, you will also need to individually recreate the foreign keys in the other tables.

samartioli commented Aug 28, 2017

Should you reindex after changing the primary key?

prajaktabanne commented Aug 31, 2017

thanks

amineds commented Nov 15, 2017

helpful ! cheers

MikilchenkoMarina commented Dec 6, 2017

thank you !!!

osweet commented Mar 13, 2018

Thanks

kokovych commented Aug 2, 2018

Great job 👍

kaggwachristopher commented Sep 9, 2018

Thanks alot

saintbyte commented Oct 1, 2018

Спасибо

vwalker04 commented Feb 2, 2019

beautiful! thanks!

naldi28 commented Apr 5, 2019

Thank you so much. It's work

elomariAchraf commented Oct 15, 2019

thanks alot

S00ahKim commented Feb 18, 2020

thank you!

mertyertugrul commented Apr 23, 2020

cheers, thank you.

anoop-sharma commented Jul 8, 2020

Thank you for good explanation.

jwatte commented Jan 16, 2021

If you don't need to rename the columns, you can just do this in a single statement:

ALTER TABLE mytable
DROP CONSTRAINT mytable_pkey,
ADD PRIMARY KEY(lastname, firstname, birthdate); -- or whatever

venriq commented Jan 25, 2021

I'd recommend having some Unique Index backing up the Primary Key before deleting it.
Imagine querying a large table on Production, with millions of rows where the queries depend on the Primary Key (PK) to run efficiently, but suddenly the PK is gone. The queries will pay a high penalty, considering that recreating the PK will take some time depending on the table's size.
Also, always consider creating indices with the CONCURRENTLY option, so the table is not locked.

DominusKelvin commented Nov 26, 2021

Thanks!

TechWithTy commented Feb 1, 2022

stetodd commented Feb 17, 2022

Thanks

marcusflat commented May 14, 2022

Thanks a lot !!

marcbachmann commented Jul 12, 2022

As @venriq mentioned, creating the new index first could be better.
Postgres could reuse other indexes during creation, if there's already one with the same columns.

I'm using that one to switch a multi column index:

ALTER TABLE <table_name> RENAME CONSTRAINT <table_name>_pkey TO <table_name>_pkeyold;
CREATE UNIQUE INDEX <table_name>_pkey ON <table_name> (id, namespace);
ALTER TABLE <table_name> DROP CONSTRAINT <table_name>_pkeyold;
ALTER TABLE <table_name> ADD PRIMARY KEY USING INDEX <table_name>_pkey;