How can I change Referential Integrity from a Unique Key Constraint to a Primary Key?

Question

(without dropping foreign keys)

I have migrated a SQL Server CE database to SQL Server 2012, however, referential integrity was enforced via unique key constraints on the surrogate INT identity columns.

e.g.

create table Table1
(
   Table1ID INT NOT NULL,
   CONSTRAINT U_000001 UNIQUE(Table1ID)
);

CREATE TABLE Table2
(
   Table2ID INT NOT NULL,
   Table1ID INT NOT NULL,
   CONSTRAINT FK_T2_T1 FOREIGN KEY(Table1ID) REFERENCES Table1(Table1ID)
);

I would like to convert the unique key constraints to primary keys, for ERD diagramming and ORM reasons.

I have added the primary keys, but cannot drop the UKC, because RI of dependent columns is already 'baked into' the UKC despite the alternative PK now being available.

ALTER TABLE Table1 ADD CONSTRAINT PK_Table1 PRIMARY KEY (Table1ID);

ALTER TABLE Table1 DROP CONSTRAINT U_000001;

Fails with

Msg 3725, Level 16, State 0, Line 1
The constraint 'U_000001' is being referenced by table 'Table2', foreign key constraint 'FK_T2_T1'.

So my question is, is there any way to change the RI checks on foreign keys to use the PRIMARY KEY, and not the UKC as is currently the case, without having to drop the foreign keys on referencing tables first?

I have prepared a small SQL Fiddle here.

Answer 1

There's currently no way to modify which unique index a foreign key constraint is "attached to" because this is supposed to be an internal implementation detail, the same way that a unique constraint uses a unique index behind the scenes to enforce the constraint.

In fact, as you saw, if you have multiple candidate indexes, the one the foreign key attaches to seems non-deterministic (it might go by index id, index width, or something else; I haven't played with it in-depth), and it cannot be specified in the DDL statement either (memory says there's a Connect item requesting that feature, though).

So in this case, at least for now, you'll need to drop both the foreign key and the existing unique constraint before re-establishing the relationship, to ensure your change script works correctly in all scenarios.