Add primary key to existing table

sql sql-server sql-server-2008 constraints primary-key
jay picture jay · Aug 3, 2012 · Viewed 688.8k times · Source

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

  • persionId
  • Pname
  • PMid
  • Pdescription
  • Pamt

When I created this table, I set PersionId and Pname as the primary key.

I now want to include one more column in the primary key - PMID. How can I write an ALTER statement to do this? (I already have 1000 records in the table)

Answer

Joe G Joseph picture Joe G Joseph · Aug 3, 2012

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'

Related questions

SQL Server 2008- Get table constraints

Could you help me frame a query that retrieves the constraints in all the tables, the count of constraints in each table, and also display NULL for tables that do NOT have any constraints. This is what I have so …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More