intro
Learn to add a primary key to existing SQL tables with ALTER TABLE in SQL Server, MySQL, PostgreSQL, and Oracle.
Suppose you have a table created without a primary key. Yes, that is actually possible—especially in legacy systems or when the schema wasn’t fully defined. Now, adding a primary key to an existing table enforces uniqueness, improves JOIN performance, and guarantees referential integrity.
This article guides you through the process of how to add a primary to an existing table!
General Workflow for Key Creation
Before you add a key to an existing table, you have this workflow clear in mind:
STEP 1: Identify candidate column(s): Choose one (or more) columns that uniquely identify each row. If a single column is not enough, you will end up with a composite primary key. Otherwise, you can always decide to add a new incremental column exactly for this reason.
STEP 2: Check for duplicates and NULLs: Verify sure that the column(s) you have chosen do not contain any duplicates or NULL values. To do so, you can run the following query (assuming col_1 is the column you identified as a potential key):
1
-- Find duplicates
2
SELECT col_1, COUNT (*)
3
FROM your_table
4
GROUP BY col1
5
HAVING COUNT (*) > 1;
6
7
-- Find NULLs
8
SELECT COUNT (*)
9
FROM your_table
10
WHERE col_1 IS NULL;
STEP 3: Make the column(s) NOT NULL: Before adding a PRIMARY KEY constraint, You must forbid NULLs.
STEP 4: Add the PRIMARY KEY constraint: This creates a UNIQUE index behind the scenes, making the selected column(s) the key of your table.
General SQL Syntax to Add a Key to a Table
Below is the syntax to add a key to an existing table:
1
ALTER TABLE your_table
2
ADD CONSTRAINT constraint_name
3
PRIMARY KEY (column_1 [, column_2, ...]);
Syntax Differences by Dialect
While the core idea—make the column NOT NULL, then add the primary key—remains the same, some SQL DDL keywords may vary depending on the database you're operating on. Explore them!
MySQL
1
-- 1. Ensure NOT NULL (restate full column definition):
2
ALTER TABLE your_table
3
MODIFY column_1 INT NOT NULL;
4
5
-- 2. Add PK (engine auto-names it “PRIMARY”):
6
ALTER TABLE your_table
7
ADD PRIMARY KEY (column_1);
1
-- 1. Ensure NOT NULL:
2
ALTER TABLE your_table
3
ALTER COLUMN column_1 SET NOT NULL;
4
5
-- 2. Add PK with explicit name:
6
ALTER TABLE your_table
7
ADD CONSTRAINT pk_your_table
8
PRIMARY KEY (column_1);
Discover more in our article on PostgreSQL primary keys.
Notes:
1
ALTER TABLE your_table ADD PRIMARY KEY(column_1);
Microsoft SQL Server
1
-- 1. Ensure NOT NULL (restate type):
2
ALTER TABLE dbo.your_table
3
ALTER COLUMN column_1 INT NOT NULL;
4
5
-- 2. Add clustered PK by default:
6
ALTER TABLE dbo.your_table
7
ADD CONSTRAINT PK_your_table
8
PRIMARY KEY CLUSTERED (column_1);
Instead, to add a non-clustered PK if you already have a clustered index, you need to run:
1
ALTER TABLE dbo.your_table
2
ADD CONSTRAINT PK_your_table
3
PRIMARY KEY NONCLUSTERED (column_1);
Oracle
1
-- 1. Ensure NOT NULL:
2
ALTER TABLE your_table
3
MODIFY (column_1 NOT NULL);
4
5
-- 2. Add PK:
6
ALTER TABLE your_table
7
ADD CONSTRAINT PK_YOUR_TABLE
8
PRIMARY KEY (column_1);
Notes:
Composite-Key Creation Example
This is how you can add a composite key on two columns (student_id and course_id) to an existing table in SQL:
1
ALTER TABLE enrollments
2
ADD CONSTRAINT pk_enrollments
3
PRIMARY KEY (student_id, course_id);
For example, use composite keys for many-to-many (n-n) and many-to-one (m-n) relationship tables, where the combination of columns must be unique.
Performance and Locking Behavior
Adding a primary key to a large table will likely lock the table and could be time-consuming since it has to verify uniqueness and build an index. On huge tables, do this during maintenance windows if possible.
Some DBs (like MySQL) might copy the whole table if you add a primary key late (especially older MySQL versions with certain engines). Newer versions with ALGORITHM=INPLACE improvements might do it without full copy. Still, plan for it as a potentially heavy operation.
Key notes:
Summary
To add a primary key to an existing table, start by selecting the appropriate column or columns. Clean the data by eliminating duplicates and ensuring there are no NULL values. Then, apply the SQL commands specific to your database: make the column NOT NULL and define it as a PRIMARY KEY. Finally, verify the change by describing the table or checking the system catalog. Need help? Tools like DbVisualizer makes database management much easier. Try it for free today!
