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

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)

Solution 1

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'

Solution 2

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

Solution 3

-- 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

Solution 4

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)

Solution 5

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

View more solutions

Comments

[Nick.McDermaid]

Are you sure? this means you are allowed to have duplicate personId in your table. This in turn means if you join from a transaction (many) type table to this table on this key alone you'll get duplicate records, leading to 'double counting' of transaction records.

[lanartri]

indeed, this is a VERY bad idea. Your PK sould be on"persionId", that's it

[CHarris]

I thought only one column in a table should be set as the primary key?

[Kristen Hammack]

@ChristopheHarris, sometimes it makes sense to have more than one column as the primary key. A one-to-many or many-to-many relationship table will likely have 2 or more foreign key columns making up the primary key, since it is only possible to uniquely identify a record if you know the values of all of the primary key columns. However, in the OP's case, it's unlikely that this is really what he was wanting.

[kloddant]

@Kristen Hammack Even in the case of M2M relationships, it is probably better to have the intermediate table have a separate primary key and then put a unique together constraint on the two foreign keys.

[Kristen Hammack]

@kloddant I think we might be saying the same thing here? I'm talking about, e.g., 2 tables A and B, each with PK Id; then a table C with PK (A.Id, B.Id) for a many-to-many relationship.

[kloddant]

@KristenHammack I don't think so, but I could be mistaken. I am talking about 2 tables A and B, each with PK Id, then a table C with PK id and a unique together index on (A.Id, B.Id). But (A.Id, B.Id) is not the PK - the PK is a separate id column in table C.

[Kristen Hammack]

@kloddant ok, so "better" in that case is mostly about whether you actually need that surrogate PK for, e.g., an ORM that can't retrieve data in any other way. Since you've still got the unique index on (A.id, B.id), there is no performance improvement in INSERTs or UPDATEs by adding the extra PK, and it's just extra data to keep up with. Another reason for the surrogate key would be security; if you need to access the relationship record from a web page and don't want users to see actual IDs, then it's useful there too.

[Shiv]

Probably non-clustered is a good option in the case of composite PKs for performance on insertion date basis if that is important for you.

[sgryzko]

Very good point about 'don't drop, FK references might already exist'. The other answers didn't work for me because of this.

[e_i_pi]

+1 for highlighting the functinoality to name your primary key. When you're running update scripts that drop-recreate PKs, it's preferable to hit named PKs than have to interrogate the information schema to figure out the name

[TT.]

Hi Ashandra. This does not answer the question correctly though. The question is to add one more column to the existing primary key, which your statement does not do.