Add primary key to existing table

user_387423 0
Viewed 870004

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)

primary-key constraints sql-server-2008 sql-server sql
11 Answers

There is already an primary key in your table. You can't just add primary key,otherwise will cause error. Because there is one primary key for sql table.

First, you have to drop your old primary key.

MySQL:

ALTER TABLE Persion
DROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

ALTER TABLE Persion
DROP CONSTRAINT 'constraint name';

You have to find the constraint name in your table. If you had given constraint name when you created table,you can easily use the constraint name(ex:PK_Persion).

Second,Add primary key.

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persion ADD PRIMARY KEY (PersionId,Pname,PMID);

or the better one below

ALTER TABLE Persion ADD CONSTRAINT PK_Persion PRIMARY KEY (PersionId,Pname,PMID);

This can set constraint name by developer. It's more easily to maintain the table.

I got a little confuse when i have looked all answers. So I research some document to find every detail. Hope this answer can help other SQL beginner.

Reference:https://www.w3schools.com/sql/sql_primarykey.asp

user_77867390

alter table[Person] add ID int primary key IDENTITY (1,1)

This will add primary key and indentity seed and populate the new column.

user_125396110
Related