How to Add a Primary Key in SQL Server

Why add a primary key after table creation?

Late modeling, data import, or legacy systems may leave tables without a primary key. ALTER TABLE … ADD CONSTRAINT lets you enforce uniqueness without recreating the table.

What is the basic ALTER TABLE syntax?

The command attaches a PRIMARY KEY constraint to one or multiple columns. Optionally specify CLUSTERED | NONCLUSTERED.

How do I add a single-column primary key?

Use one numeric, unique, NOT NULL column—typically an identity column like id.

ALTER TABLE Customers
ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (id);


Can I add a composite primary key?

Yes—combine columns that together guarantee uniqueness.

ALTER TABLE OrderItems
ADD CONSTRAINT PK_OrderItems PRIMARY KEY (
order_id, product_id
);


Example: Fix missing keys in an ecommerce schema

The following statements enforce primary keys on previously imported CSV data.

-- Customers
ALTER TABLE Customers
ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (id);

-- Orders
ALTER TABLE Orders
ADD CONSTRAINT PK_Orders PRIMARY KEY (id);

-- Products
ALTER TABLE Products
ADD CONSTRAINT PK_Products PRIMARY KEY (id);


How do I choose CLUSTERED vs NONCLUSTERED?

CLUSTERED reorders data pages by the key—great for range scans on id. NONCLUSTERED leaves the table order intact, useful when a table already has a clustered index.

What happens to existing duplicates?

SQL Server refuses to add the constraint if duplicate or NULL values exist. Clean or deduplicate data first:

WITH dups AS (
SELECT id, COUNT(*) AS c FROM Customers GROUP BY id HAVING COUNT(*)>1
)
DELETE c
FROM Customers c
JOIN dups d ON d.id = c.id;


Best practices for adding primary keys

1. Use integer surrogate keys for performance.
2. Name constraints with the PK_TableName pattern.
3. Create keys in maintenance windows when tables are large.
4. Combine the operation with foreign key creation for referential integrity.