- Primary Key can be added when the table is created, as part of the table definition.
- Primary Key can be added to an existing table, provided that no other PRIMARY KEY constraint already exists.
How to CREATE Primary Key on existing table?To explain this, I'll create a table without Primary Key constraint. Below is T-SQL to create a TestPK table:
IF OBJECT_ID('TestPK','U') IS NOT NULL
DROP TABLE TestPK
CREATE TABLE TestPK (
[ID] [int] NOT NULL,
INSERT INTO TestPK (ID,Name,DoB,DeptNo) VALUES
SELECT ID,Name,DoB,DeptNo FROM TestPK (NOLOCK)
Now ALTER the table to ADD Primary Key
Execute below T-SQL code to add primary key:
ALTER TABLE TestPK
ADD CONSTRAINT TestPK_PrimaryKey_ID PRIMARY KEY (ID)
Note: Before adding a PRIMARY KEY constraint to an existing column(s) in the table, ensure that the existing data follows the rules for primary keys:
1. No NULL values 2. No duplicate values
If a PRIMARY KEY constraint is added to a column that has duplicate or null values, SQL Server returns an error and does not add the constraint. It is not possible to add a PRIMARY KEY constraint that violates these rules.