Preserve a foreign key using a uniqueness constraint while dropping a primary constraint

Question

I have a SQL Server database I mostly interact with using EF Core in a .NET project: I'll analogize my domain logic for simplicity.

I have a Fruits table holding general fruit data in columns Weight, Volume, Tastiness. But, due to inheritance, I also have a variety of other tables for specific fruit data (Grapes with Size, Squishiness; Bananas with Length, Girth, Curvature; Oranges with Acidity, Color).

At the beginning of my project, I decided to use a natural PK for Fruits called Name. To connect fruit type tables to the Fruits table, I made the PK of those tables an FK to Fruits.Name.

However, it's not unusual for users to enter the wrong fruit name and not realize until after they've already entered all the fruit data, so I decided I should finally give them the ability to update Fruits.Name. EF doesn't like this because once you change the PK it doesn't know what row it should be updating. This seems perfectly reasonable, so I decided I should change my PK to a surrogate.

My plan was to:

Add an identity column
Add a uniqueness constraint to my Name column
Remove the primary constraint from Name
Add the primary constraint to the identity column

My hope was adding an explicit uniqueness constraint before removing the primary constraint would allow the foreign keys to be cool with the situation. Instead, I get an error

The constraint 'PK_Fruit' is being referenced by table 'Grapes', foreign key constraint 'FK_Grapes_Fruits'

My hypothesis is what I want to do can't be achieved without dropping the foreign keys and adding them back afterwards. The problem with this is there are a LOT of fruit types (way more than just Grapes, Bananas, and Oranges).

It makes sense to me the uniqueness constraint should be able to bypass the error.

How can I use a uniqueness constraint to bypass this error?

Is the primary key currently also your clustered index key? If so what is your desired clustered index after this change? — Martin Smith
– Martin Smith, Commented Oct 10 at 18:27
@MartinSmith Yes, Fruits.Name is currently the PK and clustered index. Ideally, Name will still be the clustered index as we do very little writing but read a lot using the Name column. However, if that actually has an effect on whether or not this is possible, it's not that big a deal. — Mr. Kloud7
– Mr. Kloud7, Commented Oct 10 at 18:44
Even dropping and recreating the FK, if continuing to point at the Name column, is going to cause problems in the scenario where you want to rename something. Renaming "Appple" to "Apple" for instance will have related records linked to "Appple" which will block attempts to update. This is why tables should use meaningless PKs/FKs so meaningful details (like Name) can be updated if needed. I'd suggest biting the bullet and implementing meaningless (I.e. ID) PKs /w FKs referencing those instead of "Name". Avoids issues and generally lighter on indexing resources. — Steve Py
– Steve Py, Commented Oct 12 at 23:33
@StevePy I've got cascade update and delete on for these situations. — Mr. Kloud7
– Mr. Kloud7, Commented Oct 14 at 23:08

Martin Smith · Accepted Answer · 2025-10-12 09:29:06Z

Foreign key constraints in SQL Server are bound to a specific index (see key_index_id in sys.foreign_keys).

You can't drop the index without first dropping the FK constraint.

You can create a duplicate unique index and foreign key and hope that the second foreign key gets bound to the other index (IME it prefers binding to non clustered indexes than clustered indexes given the choice (*) so I would expect it will here). If it does you can then drop the original PK and FK without leaving the table without a foreign key constraint on Name at any point.

The below works (Fiddle) but as written you just end up with a somewhat unplanned situation where your clustered index key is changed from FruitName to FruitId.

DROP TABLE IF EXISTS FruitReference, Fruits;

GO

CREATE TABLE Fruits
(
Name VARCHAR(50) NOT NULL CONSTRAINT PK_Fruits PRIMARY KEY,
Weight DECIMAL(10,4) NOT NULL
)

CREATE TABLE FruitReference
(
FruitName VARCHAR(50) CONSTRAINT FK_FruitReference_Fruits FOREIGN KEY REFERENCES Fruits
)

CREATE UNIQUE INDEX UIX_Fruits_Name ON Fruits(Name)
ALTER TABLE FruitReference ADD CONSTRAINT FK_FruitReference_Fruits2 FOREIGN KEY (FruitName) REFERENCES Fruits(Name)

SELECT name, key_index_id 
FROM sys.foreign_keys
WHERE parent_object_id = object_id('FruitReference')

name	key_index_id
FK_FruitReference_Fruits	1
FK_FruitReference_Fruits2	2

Validate that the above result set shows the foreign keys are referencing different indexes. If they are you can then proceed with...

ALTER TABLE FruitReference DROP CONSTRAINT FK_FruitReference_Fruits
ALTER TABLE Fruits DROP CONSTRAINT PK_Fruits
ALTER TABLE Fruits ADD FruitId INT NOT NULL IDENTITY CONSTRAINT PK_PK_Fruits PRIMARY KEY;

Personally I can't envisage a situation where I would use something like the above but it is, nonetheless, an option.

Dropping and recreating the clustered index means that the table will become a heap and all the row locators in all the non clustered indexes on the table will need to be first updated to be a RID and then updated to the clustered index key.

Probably I would create a copy of Fruits and get that set up with the desired eventual state w.r.t. primary key/foreign key/clustered index and kept synchronised with the main table. All this preparatory work can happen without worrying about table locks blocking BAU queries. Then (inside a transaction) replace Fruits with the copy via rename operations.

(*) This answer on the DBA site indicates that the exact syntax used can also influence it to bind to the primary key specifically rather than some other unique index if this behaviour is desired.

Relevant dba.stackexchange.com/questions/20168/… also I think Paul or Erik wrote an article but can't find it now.
You're definitely a bit above my head on this one, but it got me curious enough to lead to a resolution (updated in question): thanks!

Lajos Arpad · Accepted Answer · 2025-10-10 19:46:25Z

According to my test this is not possible. This is how I tested:

create table foo(
    [name] varchar(255) primary key
);

create table bar(
    [name] varchar(255) foreign key ([name]) references foo([name])
);

ALTER TABLE dbo.foo
ADD ID INT IDENTITY(1,1);

insert into foo([name])
values('a');

insert into bar([name])
values('a');

CREATE UNIQUE NONCLUSTERED INDEX [unique_name] ON [foo]
([name]);

ALTER TABLE foo   
ADD CONSTRAINT name_unique UNIQUE ([name]); 

DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);

SELECT @table = N'dbo.Student';

SELECT @sql = 'ALTER TABLE foo' 
    + ' DROP CONSTRAINT ' + name + ';'
    FROM sys.key_constraints
    WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID('dbo.foo');

EXEC sp_executeSQL @sql;

select * from bar;

Basically I create a table with a primary key and another one which references it and I insert values to both. Then I add the identity field to the first one that should later be the primary key and a unique index on name. However, when I attempt to drop the primary key, I get the error of

Msg 3725, Level 16, State 1, Server 8982719c2599, Line 1
The constraint 'PK__foo__72E12F1A38E727EF' is being referenced by table 'bar', foreign key constraint 'FK__bar__name__38996AB5'.
Msg 3727, Level 16, State 1, Server 8982719c2599, Line 1
Could not drop constraint. See previous errors.

even though I already had a unique index. The foreign key referenced the unique index. I can also try with nocheck constraint:

SELECT @sql = 'ALTER TABLE foo' 
    + ' DROP CONSTRAINT ' + name + ' NOCHECK CONSTRAINT ALL;'
    FROM sys.key_constraints
    WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID('dbo.foo');

but when running

create table foo(
    [name] varchar(255) primary key
);

create table bar(
    [name] varchar(255) foreign key ([name]) references foo([name])
);

ALTER TABLE dbo.foo
ADD ID INT IDENTITY(1,1);

insert into foo([name])
values('a');

insert into bar([name])
values('a');


CREATE UNIQUE NONCLUSTERED INDEX [unique_name] ON [foo]
([name]);

ALTER TABLE foo   
ADD CONSTRAINT name_unique UNIQUE ([name]); 

DECLARE @table NVARCHAR(512), @sql NVARCHAR(MAX);

SELECT @table = N'dbo.Student';

SELECT @sql = 'ALTER TABLE foo' 
    + ' DROP CONSTRAINT ' + name + ' NOCHECK CONSTRAINT ALL;'
    FROM sys.key_constraints
    WHERE [type] = 'PK'
    AND [parent_object_id] = OBJECT_ID('dbo.foo');

EXEC sp_executeSQL @sql;

ALTER TABLE foo CHECK CONSTRAINT ALL;

ALTER TABLE foo
   ADD CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (ID);

select * from bar;

SELECT name
FROM   sys.key_constraints
WHERE  [type] = 'PK'
       AND [parent_object_id] = Object_id('dbo.foo');

EXEC sp_executeSQL @sql;

select * from bar;

it complains about NOCHECK syntax, albeit it only does if I attempt to add the primary key.

Conclusion

You can create a foreign key that references a primary key and you can create a foreign key that references a unique key, but you cannot change a foreign key that referenced a primary key to refer the same field after you added a unique constraint, because you can't drop the primary key constraint while the foreign key is in place.

I do not know the reason, but I guess it is because the foreign key and the primary key were already indexed and pairing it with the new unique key would require a brand new indexing. The very thing you wanted to avoid.

It seems it will be necessary after all and even though it will take a while to drop the foreign key and then recreate it, this seems to be the best solution. If you are worried, then you can create a backup of the database, import the backup into a place you are not worried of, test the script, measure the times and see whether your worry was justified.

philipxy · Accepted Answer · 2025-10-15 19:50:20Z

Self-answer based on @MartinSmith's answer:

The first 2 lines of code ran fine:

--Add identity column
ALTER TABLE dbo.Fruits ADD Id INT IDENTITY
--Add uniqueness to Name
ALTER TABLE dbo.Fruits ADD CONSTRAINT UQ_Name UNIQUE (Name);

Because my FKs were all named consistently, I used this query to view the key_index_id column Martin mentioned:

select s.key_index_id, s.name, s.parent_object_id 
from sys.foreign_keys s where s.name like '%FK_%Fruit%'

All the key_index_ids were 1, but the index_id of my new unique constraint was 8 based on the following query:

select index_id, name from sys.indexes where name = 'UQ_Name'

I tested manually updating the index_id of the keys to 8 but received the error:

ad hoc update to system catalogs are not allowed.

Instead, on my Grapes table in SSMS, I right clicked FK_Grapes_Fruit => Script Key as => DROP and CREATE To => New Query Editor Window, and combined that default template (minus the GO statements) with the sys tables to generate code to drop and remake every FK with a connection to Fruit:

/*Must drop/remake FKs on Fruit type tables.*/
select 'ALTER TABLE [dbo].[' + o.name + '] DROP CONSTRAINT [' + f.name + ']
ALTER TABLE [dbo].[' + o.name + ']  WITH NOCHECK ADD  CONSTRAINT [' + f.name + '] FOREIGN KEY([Name])
REFERENCES [dbo].[Fruits] ([Name])
ALTER TABLE [dbo].[' + o.name + '] CHECK CONSTRAINT [' + f.name + ']'
    from sys.foreign_keys f 
        join sys.objects o on f.parent_object_id = o.object_id
    where f.name like '%FK_%_Fruit%'

I ran all the resulting scripts in their own window and then reran the following query:

select s.key_index_id, s.name, s.parent_object_id 
from sys.foreign_keys s where s.name like '%FK_%Fruit%'

This time, all the key_index_id values were 8. I was then able to run my final 2 lines of code with no issue.

--Remove PK from Name
ALTER TABLE dbo.Fruits DROP CONSTRAINT PK_Fruits;
--Add PK to Id
ALTER TABLE dbo.Fruits ADD CONSTRAINT PK_Fruits PRIMARY KEY(Id)

Based on how the FKs are tied to a specific Index, no, you can't move the FKs if they're already tied to the PK index. However, the FKs seem to give priority to either unique indices or the last indices created on a column (one or the other, I'm not sure which).

Collectives™ on Stack Overflow

Preserve a foreign key using a uniqueness constraint while dropping a primary constraint

3 Answers 3

2 Comments

Conclusion

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Conclusion

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related