Set column as primary key if the table doesn't have a primary key

Question

I have a column in db which has 5 columns but no primary key. One of the columns is named myTable_id and is integer.

I want to check if the table has a primary key column. If it doesn't, then make myTable_id a primary key column and make it identity column. Is there a way to do this?

I tried with this:

ALTER TABLE Persons DROP CONSTRAINT pk_PersonID  ALTER TABLE Persons ADD PRIMARY KEY (P_Id) 

and I get syntax error in Management studio.

Answer 1

This checks if primary key exists, if not it is created

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'Persons'  AND TABLE_SCHEMA ='dbo') BEGIN    ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id) END ELSE BEGIN    -- Key exists END 

fiddle: http://sqlfiddle.com/#!6/e165d/2