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