SQL Server : set primary key without dropping table and content [duplicate]

Question

Is it possible to set the primary key and auto increment on a SQL Server table without dropping and recreating the table, and losing all it's data?

Answer 1

Yes of course! You just add a new column, and it an INT IDENTITY and add a primary key constraint to it:

 ALTER TABLE dbo.YourTable
 ADD ID INT IDENTITY(1,1) NOT NULL

 ALTER TABLE dbo.YourTable
 ADD CONSTRAINT PK_YourTable PRIMARY KEY (ID)

Answer 2

If there is an existing primary key, you must first drop it:

IF EXISTS (SELECT * FROM sys.key_constraints
           WHERE type = 'PK' AND parent_object_id = OBJECT_ID('MyTable')
           AND Name = 'PK_MyTable')
    ALTER TABLE MyTable DROP CONSTRAINT PK_MyTable

If you are adding a column to be used as a primary key, then you can simply add it:

ALTER TABLE MyTable ADD MyKey INT IDENTITY

Then, you can set this column as your table's primary key:

ALTER TABLE MyTable ADD CONSTRAINT PK_MyTable PRIMARY KEY(MyKey)