Add primary key to existing table

Question

I have an existing table called Persion. In this table I have 5 columns:

• persionId
• Pname
• PMid
• Pdescription
• Pamt

PersionId and Pname were the primary keys I chose for this table when I was creating it.

I now want to add PMID as a new column to the primary key. How do I construct this in an ALTER statement? (The table already contains 1000 records.)

Can someone help me with this?

Answer 1

You have to 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'