2

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

4
  • 2
    I don't think it's possible. You need to first drop the foreign key and re-recreate them pointing to the (new) PKs Oct 24, 2013 at 7:31
  • Interestingly enough, SQL seems determined to use the UKC ahead of the PK even if the RI is created afterwards, e.g. sqlfiddle.com/#!6/34c52
    – StuartLC
    Oct 24, 2013 at 10:56
  • I think this is more what a_horse suggested: SQL-Fiddle Oct 24, 2013 at 15:17
  • @ypercube - indeed, that's what I had feared I had to do in the original fiddle above. I guess I was hoping there was a back door hack to somehow get Sql to switch / influence the way in which it enforced the FK.
    – StuartLC
    Oct 24, 2013 at 18:34

1 Answer 1

2

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.

1
  • Thanks Jon - will generate drop scripts for the UKC and FK's and recreate the FK's.
    – StuartLC
    Oct 24, 2013 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.