Remove 1 primary key column out of 3

December 27, 2023

I have a table that has a composite primary key made up from 3 columns.

I’ve tried to remove one of the columns (SomeColumn1) but I can’t remove it because of it’s the primary key.

Then I tried this code

DECLARE @ConstraintName NVARCHAR(MAX);

SELECT @ConstraintName = CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'PrimaryDeneme1'

ALTER TABLE PrimaryDeneme1
DROP CONSTRAINT PK_PrimaryDeneme1;

ALTER TABLE PrimaryDeneme1
DROP COLUMN SomeColumn1

After that the column is removed but at the same time primary key is gone.

I want to remove a primary key column but not unset the primary key.

>Solution :

Whenever you want to remove a column that’s being referenced by a constraint of any type, you must first drop the constraint and only then you can drop the column.
In this case, Once you’ve dropped the constraint and the column, you must create a new primary key constraint:

-- Drop the primary key constraint
ALTER TABLE PrimaryDeneme1
DROP CONSTRAINT PK_PrimaryDeneme1;

-- Drop the column
ALTER TABLE PrimaryDeneme1
DROP COLUMN SomeColumn1

-- Add a new primary key constraint:
ALTER TABLE PrimaryDeneme1
ADD CONSTRAINT PK_PrimaryDeneme1 PRIMARY KEY (PkColumn1, PkColumn2);

Don’t forget to replace PkColumn1 and PkColumn2 with your actual column names.