How to alter an already created table to drop and re-create the primary key?

Question

I have this table:

Create table Product (
    [Id] int primary key not null,
    [Name] varchar (250),
    [Price] money
)

The table is already created, and now I want to make [Name] primary key with [Id].

I don't want to re-create the table, instead I just want to ALTER it.

Have you read into the [ALTER statement](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-ver15), if not, I would recommend. — Trevor, Sep 03 '20 at 19:06
Why would you want a combined PK? Thats going to cause all sorts of issues down the line. — Dale K, Sep 03 '20 at 22:36

score 2 · Answer 1 · answered Sep 03 '20 at 19:04

2

ALTER TABLE Product DROP PRIMARY KEY, ADD PRIMARY KEY(Id, Name);

answered Sep 03 '20 at 19:04

Nick Juelich

428
2
13

1

How's that can be upvoted! That's a wrong T-SQL – Ilyes Sep 03 '20 at 19:11
2

the table is already created – Ahmed Nouressaid Sep 03 '20 at 19:13

Ilyes · Answer 2 · 2020-09-03T19:41:48.107

You should always explicitly name your constraints. As you didn't specify the CONSTRAINT name, SQL Server will generate a default name for your PK eg: PK__Product__9CC368536561EF8B.

Thus, you need to delete it manually using SSMS, or by using Dynamic SQL, querying sys.key_constraints to get the name and drop the constraint as it already answered here.

Furthermore, you need also to set the column Name to NOT NULL before creating the new PK, and make sure all the rows if any doesn't 've NULL value

ALTER TABLE Product ALTER COLUMN Name varchar (250) NOT NULL;

Here is a db-fiddle

score 0 · Answer 3 · answered Sep 03 '20 at 19:11

To modify a primary key you can execute an ALTER TABLE Product DROP PRIMARY KEY statement to drop the existing primary key, and then execute an ALTER TABLE ALTER column-name column-alteration statement to set the new primary key for the table

ALTER TABLE Product DROP PRIMARY KEY, ADD PRIMARY KEY(Id, Name);

score 0 · Answer 4 · edited Sep 03 '20 at 19:44

I replicated your problem along with a solution...

here I find the name of your table's primary

select CONSTRAINT_NAME 
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
where TABLE_NAME = 'Product' and CONSTRAINT_TYPE = 'PRIMARY KEY'

take the return from the above query and replace (PK_ Product_ 3214EC07822AF903) in the query below

alter table Product drop constraint PK__Product__3214EC07822AF903

primary key cannot be null

alter table Product alter column [Name] varchar (250) NOT NULL

finally, setting the new primary key

alter table Product add constraint PK_Product primary key ([Name])

The OP wants a combined PK (Id, Name) – Ilyes Sep 03 '20 at 19:43 — Ilyes, Sep 03 '20 at 19:43

How to alter an already created table to drop and re-create the primary key?

4 Answers4