Add primary key to existing table

Question

drop constraint and recreate it

alter table Persion drop CONSTRAINT <constraint_name>

alter table Persion add primary key (persionId,Pname,PMID)

edit:

you can find the constraint name by using the query below:

select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='Persion'
and type_desc LIKE '%CONSTRAINT'

I think something like this should work

-- drop current primary key constraint
ALTER TABLE dbo.persion 
DROP CONSTRAINT PK_persionId;
GO

-- add new auto incremented field
ALTER TABLE dbo.persion 
ADD pmid BIGINT IDENTITY;
GO

-- create new primary key constraint
ALTER TABLE dbo.persion 
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);
GO

-- create new primary key constraint
ALTER TABLE dbo.persion 
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);

is a better solution because you have control over the naming of the primary_key.

---

It's better than just using

ALTER TABLE Persion ADD PRIMARY KEY(persionId,Pname,PMID)

which yeilds randomized names and can cause problems when scripting out or comparing databases

If you add primary key constraint

ALTER TABLE <TABLE NAME> ADD CONSTRAINT <CONSTRAINT NAME> PRIMARY KEY <COLUMNNAME>  

for example:

ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)