How to CREATE Primary Key on existing table?

Only single PRIMARY KEY constraint can be added to a table.
  • 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
GO
CREATE TABLE TestPK (
    [ID] [int] NOT NULL,
    [Name] [varchar](50),
    [DoB] [date],
    [DeptNo] [tinyint]
)
GO
INSERT INTO TestPK (ID,Name,DoB,DeptNo) VALUES
(1,'Name1','1983-04-07',1),
(2,'Name2','1992-10-21',3),
(3,'Name3','1981-02-17',1),
(4,'Name4','1975-11-23',2)
GO
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.

No comments:

Post a Comment