Sql-script: How to write ALTER statements to set Primary key on an existing table?

sql

best-practices

database

sql-scripts

byNikita Barsukov·Dec 6, 2024

Change your Primary Key with this command:

ALTER TABLE your_table
ADD CONSTRAINT your_constraint PRIMARY KEY (your_column_1, your_column_2);

Remember: your_table is the table to be altered, your_constraint is any name for the constraint, and (your_column_1, your_column_2) are the columns. If there's only one column, just mention one.

Let's dive deeper into modifying primary keys

Tread cautiously: It's not just about running a script

Before charging in like a bull in a china shop, let's make sure we don't break anything. Back up your table before altering primary keys because things can go wrong—just like milk spilt.

Steps to safely alter a primary key

Identify the present primary key: Run a query to find the current primary key constraint name before dropping it.
Check the chain of impact: Carry out dependency checks. It's important to know how your actions ripple across related tables.
Ditch the old, welcome the new: Drop old primary key with ALTER TABLE DROP CONSTRAINT and create the new one with ALTER TABLE ADD CONSTRAINT.

Code samples and some tips

Old key out, new key in

-- Why did the primary key break up with the table?
-- Because they had a DROP-relationship.
ALTER TABLE your_table
DROP CONSTRAINT old_primary_key;

-- And then the table met someone new.
ALTER TABLE your_table
ADD CONSTRAINT new_primary_key PRIMARY KEY (new_key_column);

Tip for the wise: Do this when the database is at its least active. Not everyone likes to witness breakups.

When the new primary key is more than just one column

Sometimes, the new primary key includes additional columns. In that case,

-- When the table moves on, it likes to keep its options(column) open.
ALTER TABLE your_table
ADD CONSTRAINT new_constraint PRIMARY KEY (column1, column2, ..., columnN);

Key-Note: Their order matters and can drastically impact performance.

Taking alterations to the next level

Knowing the possible castles in the sand

Updating a table's primary key can lead to earth-shattering changes. It impacts application behaviours, database efficiency, and relationships with other tables. So, keep your team in the loop and always prep for a plan B just in case.

Syntax and compatibility: Not one size fits all

The trick is in the syntax—making sure it's tailored to suit your specific database system. Remember, altering primary keys differs between MySQL, PostgreSQL, and others.