I have a table with a primary key available; I want to add another one in primary but can't add it.

I want to add column Trndate to the primary key.

When I was try to add in primary following code

ALTER TABLE dbo.avgEnt 
ADD CONSTRAINT PK_avgent PRIMARY KEY NONCLUSTERED (srno, trndate);

I get an error:

Msg 1779, Level 16, State 0, Line 1
Table 'avgEnt' already has a primary key defined on it.

Msg 1750, Level 16, State 0, Line 1
Could not create constraint or index. See previous errors.

When I open the design of table and add primary key (SRNO, TRNDATE) then it works fine.

When I was trying to add by using the query, it does not work.

My database version is SQL Server 2014.

CodePudding user response：

A table cannot have multiple PRIMARY KEYs, this is simply not allowed, there is not work around. It is by design.

A table can, however, have multiple UNIQUE INDEXes or UNIQUE CONSTRAINTs. These are not the same as a PRIMARY KEY but do offer similar functionality, and may well provide the solution you need. A column (or set of columns) that are part of a UNIQUE INDEX can also be the target of a FOREIGN KEY, which if your "need" for a second PRIMARY KEY is so you can use it as a FOREIGN KEY then this would also fulfil that requirement.

As an example, you might have something like this:

CREATE TABLE dbo.PrimaryTable (ID int IDENTITY,
                               SomeValue varchar(10) NULL,
                               AnotherIdentifier uniqueidentifier NOT NULL
                               CONSTRAINT PK_PrimaryTable PRIMARY KEY (ID));

CREATE UNIQUE INDEX UQ_PrimaryTable_AnotherIdentifier ON dbo.PrimaryTable (AnotherIdentifier);
GO

CREATE TABLE dbo.SecondaryTable (ID int IDENTITY,
                                 PrimaryID int NOT NULL,
                                 AnotherValue decimal(12,2) NOT NULL,
                                 CONSTRAINT PK_SecondaryTable PRIMARY KEY (ID),
                                 CONSTRAINT FK_SecondaryTable_PrimaryTable FOREIGN KEY (PrimaryID) REFERENCES dbo.PrimaryTable (ID));
GO

CREATE TABLE dbo.AnotherTable (ID int IDENTITY,
                               AnotherID uniqueidentifier NOT NULL,
                               OtherValue int NOT NULL,
                               CONSTRAINT PK_AnotherTable PRIMARY KEY (ID),
                               CONSTRAINT FK_SecondaryTable_PrimaryTable_AnotherIdentifier FOREIGN KEY (AnotherID) REFERENCES dbo.PrimaryTable (AnotherIdentifier));
GO

So here you can see that dbo.SecondaryTable uses the ID column of dbo.PrimaryTable as it's FOREIGN KEY, however, dbo.AnotherTable uses the column AnotherIdentifier even though it isn't the PRIMARY KEY. This is because the column has a UNIQUE INDEX on it, meaning that data integrity can be maintained.

Otherwise, as I mentioned in the comments, you could split the table into 2 tables, and then have a 3rd table with a composite key to manage the relationship. So using the dbo.PrimaryTable you might have something like this:

CREATE TABLE dbo.PrimaryTable (ID int IDENTITY,
                               SomeValue varchar(10) NULL,
                               CONSTRAINT PK_PrimaryTable PRIMARY KEY (ID));

CREATE TABLE dbo.AnotherPrimaryTable (AnotherIdentifier uniqueidentifier NOT NULL,
                                      CONSTRAINT PK_AnotherPrimaryTable PRIMARY KEY (AnotherIdentifier));
GO
CREATE TABLE dbo.PrimaryTableLink (ID int NOT NULL,
                                   AnotherIdentifier uniqueidentifier NOT NULL,
                                   CONSTRAINT PK_PrimaryTableLink PRIMARY KEY (ID,AnotherIdentifier),
                                   CONSTRAINT FK_PrimaryTableLink_PrimaryTable FOREIGN KEY (ID) REFERENCES dbo.PrimaryTable (ID),
                                   CONSTRAINT FK_PrimaryTableLink_AnotherPrimaryTable FOREIGN KEY (AnotherIdentifier) REFERENCES dbo.AnotherPrimaryTable (AnotherIdentifier));

Which you use is up to you. It more depends on your design and requirements. I would likely only use the latter if you needed a many to many relationship, as with a one to one the UNIQUE INDEX serves the goal fine. If you wanted to use the multiple table solution for a one to one relationship, you'd likely need to also add UNIQUE INDEXes to the ID and AnotherIdentifier columns on dbo.PrimaryTableLink, which poses the question of why didn't you create said INDEX on the original column in dbo.PrimaryTable first?

CodePudding user response：

You can not alter the primary key. The only way is dropping it and then recreating it.

ALTER TABLE avgEnt DROP PRIMARY KEY, ADD PRIMARY KEY(srno,trndate);

https://www.db-fiddle.com/f/BQuEjw2pthMDb1z8NTdHv/0 check aswell