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
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)
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
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
-- 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
If you include a primary key constraint, the results will be more accurate.
ALTER TABLE <TABLE NAME> ADD CONSTRAINT <CONSTRAINT NAME> PRIMARY KEY <COLUMNNAME>
ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)
Answered by K GANGA
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.
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.
Answered by 劉鎮瑲
Post is based on https://stackoverflow.com/questions/11794659/add-primary-key-to-existing-table