SQL Server : set primary key without dropping table and content

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?

http://stackoverflow.com/questions/9158564/add-primary-key-column-in-sql-table — Mihai, Dec 23 '13 at 15:55
http://stackoverflow.com/questions/4862385/sql-server-add-auto-increment-primary-key-to-existing-table — Mihai, Dec 23 '13 at 15:56

score 6 · Answer 1 · answered Dec 23 '13 at 15:59

6

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)

answered Dec 23 '13 at 15:59

marc_s

704,970
168
1,303
1,425

score 3 · Answer 2 · answered Dec 23 '13 at 16:00

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)

SQL Server : set primary key without dropping table and content

2 Answers2