Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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 –  a_horse_with_no_name Oct 24 '13 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 '13 at 10:56
    
I think this is more what a_horse suggested: SQL-Fiddle –  ypercube Oct 24 '13 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 '13 at 18:34

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks Jon - will generate drop scripts for the UKC and FK's and recreate the FK's. –  StuartLC Oct 24 '13 at 16:23
    
@Stuart: You're welcome. –  Jon Seigel Oct 24 '13 at 19:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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