Sql: set existing column as Primary Key in MySQL

sql

database-design

data-types

error-handling

byNikita Barsukov·Nov 28, 2024

Convert an existing column into a Primary Key using ALTER TABLE and ADD PRIMARY KEY. Bear in mind that your targeted column should be unique and non-null. Here is the shorthand using your your_table and id:

ALTER TABLE `your_table` ADD PRIMARY KEY (`id`);

Just replace your_table and id with your specifics. If columns have null or non-unique values, you'll need to adjust them first.

Primary key preconditions

Before a make over of an ordinary citizen (column) into a super hero (Primary Key), few tests must be passed:

Uniqueness test — all values in column must be different, no twins allowed.
Non-Null test — Ooops, each value in column must exist, N/A isn't an option.
No Conflict test — no prior constraints or indexes that would fight with our super hero.
Training Suit test — the right working gear (data type) for Primary Key, should be equipped.

Duplicate values? Dump 'em

Ensure there's no evil twin (duplicate value) in targeted column before granting the superpowers (Primary Key):

SELECT column_name, COUNT(*) 
FROM table_name GROUP BY column_name 
HAVING COUNT(*) > 1; # Did someone say twins?

Backup first, Alter later

Following the superhero code, safety comes first. Take a backup before altering:

CREATE TABLE backup_table_name AS SELECT * FROM original_table_name; # Copying, Ctrl+C, Ctrl+V... and we're safe!

PHPMyAdmin to the rescue

If CLI commands seem like Kryptonite to you, PHPMyAdmin is like your trusty sidekick:

Fly to the Structure tab of your table.
Hit the 'Primary' 🔑 icon besides the column to transform it to super-column.

NULL values? Not on my watch

Should your column harbor any NULL values, get rid of them faster than Superman on caffeine:

UPDATE table_name SET column_name = next_value WHERE column_name IS NULL;

Avoiding conflicts - the right way

Before unleashing powers, make sure there's no potential nemesis (conflicting index or constraint):

SHOW INDEXES FROM table_name; # Let's see who else is trying to play superhero here.

Check your uniform (Data Type)

Double-check if the uniform (data type) is fitting. Numeric types usually prefer INT, while alphanumerics go with CHAR or VARCHAR.

Facing the beast (Error Handling)

"No Index Defined!" — Sent chills down your spine? It's likely to appear when we hurry for superpowers, neglecting due checks. Remember the Preconditions? They're important than my coffee.