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

Answer 2

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)

Answer 3

An IDENTITY constraint can't be added to an existing column, so how you add this needs to be your initial thought. There are two options:

1. Create a new table including a primary key with identity and drop the existing table
2. Create a new primary key column with identity and drop the existing 'P_ID' column

There is a third way, which is a better approach for very large tables via the ALTER TABLE...SWITCH statement. See Adding an IDENTITY to an existing column for an example of each. In answer to this question, if the table isn't too large, I recommend running the following:

-- Check that the table/column exist and no primary key is already on the table. 
IF COL_LENGTH('PERSONS','P_ID') IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                   WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'PERSONS')
                   -- Add table schema to the WHERE clause above e.g. AND TABLE_SCHEMA ='dbo'
BEGIN
    ALTER TABLE PERSONS    
    ADD P_ID_new int IDENTITY(1, 1)
    GO

    ALTER TABLE PERSONS 
    DROP COLUMN P_ID
    GO

    EXEC sp_rename 'PERSONS.P_ID_new', 'P_ID', 'Column'
    GO

    ALTER TABLE PERSONS 
    ADD CONSTRAINT PK_P_ID PRIMARY KEY CLUSTERED (P_ID)
    GO
END

Notes: By explicitly using the CONSTRAINT keyword the primary key constraint is given a particular name rather than depending on SQL Server to auto-assign a name.

Only include CLUSTERED on the PRIMARY KEY if the balance of searches for a particular P_ID and the amount of writing outweighs the benefits of clustering the table by some other index. See Create SQL IDENTITY as PRIMARY KEY.

Answer 4

You can check if primary key exists or not using OBJECTPROPERTY Transact SQL, use 'TableHasPrimaryKey' for the second arguments.

DECLARE @ISHASPRIMARYKEY INT;

SELECT @ISHASPRIMARYKEY = OBJECTPROPERTY(OBJECT_ID('PERSONS'), 'TABLEHASPRIMARYKEY');
IF @ISHASPRIMARYKEY IS NULL 
BEGIN
   -- generate identity column
   ALTER TABLE PERSONS
   DROP COLUMN P_ID;

   ALTER TABLE PERSONS
   ADD P_ID INT IDENTITY(1,1);

   -- add primary key
   ALTER TABLE PERSONS
   ADD CONSTRAINT PK_PERSONID PRIMARY KEY (P_ID);

END;

Answer 5

I don't think you can do that. For making a column into an identity column I think you have to drop the table entirely.