Problem
Persion is an existing table in my database. I have 5 columns in this table:
PersionId and Pname were the primary keys when I built this table.
Now I’d like to add one more column to the primary key: PMID. How can I accomplish this with an ALTER statement? (I have 1000 records in the database already.)
Asked by jay
Solution #1
Remove the limitation and replace it with a new one.
alter table Persion drop CONSTRAINT <constraint_name>
alter table Persion add primary key (persionId,Pname,PMID)
edit:
Using the query below, you can find the constraint name:
select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='Persion'
and type_desc LIKE '%CONSTRAINT'
Answered by Joe G Joseph
Solution #2
Something along these lines, I believe, 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
Answered by T I
Solution #3
-- create new primary key constraint
ALTER TABLE dbo.persion
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);
Because you have control over the naming of the primary key, this is a superior solution.
It’s preferable to just using
ALTER TABLE Persion ADD PRIMARY KEY(persionId,Pname,PMID)
This generates randomized names, which might cause issues when scripting or comparing databases.
Answered by user3675542
Solution #4
If you include a primary key constraint, the results will be more accurate.
ALTER TABLE <TABLE NAME> ADD CONSTRAINT <CONSTRAINT NAME> PRIMARY KEY <COLUMNNAME>
for example:
ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
Answered by K GANGA
Solution #5
In your table, you already have a primary key. You can’t only add the primary key; else, an error will occur. Because the sql table only has one primary key.
To begin, you must discard your old main key.
MySQL:
ALTER TABLE Persion
DROP PRIMARY KEY;
SQL Server, Oracle, and Microsoft Access:
ALTER TABLE Persion
DROP CONSTRAINT 'constraint name';
Second,Add primary key.
MySQL/SQL Server/Oracle/Microsoft Access:
ALTER TABLE Persion ADD PRIMARY KEY (PersionId,Pname,PMID);
or the one below that is better
ALTER TABLE Persion ADD CONSTRAINT PK_Persion PRIMARY KEY (PersionId,Pname,PMID);
This allows the developer to name the constraint. It’s easier to keep the table clean.
When I looked over all of the answers, I became a little perplexed. So I go over a document looking for every detail. I hope this answer is useful to other SQL newbies.
Reference:https://www.w3schools.com/sql/sql_primarykey.asp
Answered by 劉鎮瑲
Post is based on https://stackoverflow.com/questions/11794659/add-primary-key-to-existing-table