Reports

Resolution Based on @MartinSmith's response

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).

79791368