Foreign key issues

jmeyer · October 12, 2016 12:46PM

It looks like foreign keys are not being dropped if they are referencing an index and the index needs to be dropped. My settings do specify that foreign keys should be disabled

Andrew P · October 14, 2016 1:39AM

Hey Jmeyer,

Thanks for contacting us and sorry you are having this issue!
If the primary key, index, or unique constraint is the comparison key, it can't be dropped. https://documentation.red-gate.com/disp ... ct+options

That said - what version are you using currently (11.x.x.x)?
Would you mind sending in an example of this issue?

Thanks!

jmeyer · October 14, 2016 9:46AM

The comparison happens on the primary key, but the foreign key is linked to a unique index.
Reproduction:

-- Source System

USE tempdb;
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO

IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'&#91;dbo&#93;.&#91;TableXRef&#93;')
                    AND type IN (N'U') )
  DROP TABLE dbo.TableXRef;
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'&#91;dbo&#93;.&#91;TableBase&#93;')
                    AND type IN (N'U') )
  DROP TABLE dbo.TableBase;
GO

CREATE TABLE dbo.TableBase (id INT IDENTITY(1, 1)
                                   NOT NULL
                          , NaturalKey VARCHAR(20) NOT NULL
                          , CONSTRAINT PK_dbo_TableBase PRIMARY KEY CLUSTERED (id ASC) ON &#91;PRIMARY&#93;
                          , CONSTRAINT UQ_dbo_TableBase_NaturalKey UNIQUE NONCLUSTERED (NaturalKey ASC) ON &#91;PRIMARY&#93;)
ON
  &#91;PRIMARY&#93;;
GO

CREATE TABLE dbo.TableXRef (id INT IDENTITY(1, 1)
                                   NOT NULL
                          , NaturalKey VARCHAR(20) NOT NULL
                          , DummyData CHAR(4) NOT NULL
                          , CONSTRAINT PK_dbo_TableXRef PRIMARY KEY CLUSTERED (id ASC) ON &#91;PRIMARY&#93;
                          , CONSTRAINT UQ_dbo_TableBase_DummyData UNIQUE NONCLUSTERED (DummyData ASC) ON &#91;PRIMARY&#93;)
ON
  &#91;PRIMARY&#93;;
GO

ALTER TABLE dbo.TableXRef ADD CONSTRAINT
FK_dbo_TableXRef_NaturalKey_dbo_TableBase_NaturalKey FOREIGN KEY
(
NaturalKey
) REFERENCES dbo.TableBase
(
NaturalKey
); 

-- generate fake data
INSERT  INTO dbo.TableBase
        (NaturalKey
        )
SELECT  number
FROM    master.dbo.spt_values
WHERE   number BETWEEN 1 AND 100
GROUP BY number
ORDER BY number;

INSERT  INTO dbo.TableXRef
        (NaturalKey
       , DummyData
        )
SELECT  MIN(number) AS number
      , SOUNDEX(name) AS DummyData
FROM    master.dbo.spt_values
WHERE   number BETWEEN 1 AND 100
        AND SOUNDEX(name) IS NOT NULL
GROUP BY SOUNDEX(name)
ORDER BY number;

code for the destination system including data destruction to trigger data sync

-- Destination system

USE tempdb;
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO

IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'&#91;dbo&#93;.&#91;TableXRef&#93;')
                    AND type IN (N'U') )
  DROP TABLE dbo.TableXRef;
IF EXISTS ( SELECT  1
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'&#91;dbo&#93;.&#91;TableBase&#93;')
                    AND type IN (N'U') )
  DROP TABLE dbo.TableBase;
GO

CREATE TABLE dbo.TableBase (id INT IDENTITY(1, 1)
                                   NOT NULL
                          , NaturalKey VARCHAR(20) NOT NULL
                          , CONSTRAINT PK_dbo_TableBase PRIMARY KEY CLUSTERED (id ASC) ON &#91;PRIMARY&#93;
                          , CONSTRAINT UQ_dbo_TableBase_NaturalKey UNIQUE NONCLUSTERED (NaturalKey ASC) ON &#91;PRIMARY&#93;)
ON
  &#91;PRIMARY&#93;;
GO

CREATE TABLE dbo.TableXRef (id INT IDENTITY(1, 1)
                                   NOT NULL
                          , NaturalKey VARCHAR(20) NOT NULL
                          , DummyData CHAR(4) NOT NULL
                          , CONSTRAINT PK_dbo_TableXRef PRIMARY KEY CLUSTERED (id ASC) ON &#91;PRIMARY&#93;
                          , CONSTRAINT UQ_dbo_TableBase_DummyData UNIQUE NONCLUSTERED (DummyData ASC) ON &#91;PRIMARY&#93;)
ON
  &#91;PRIMARY&#93;;
GO

ALTER TABLE dbo.TableXRef ADD CONSTRAINT
FK_dbo_TableXRef_NaturalKey_dbo_TableBase_NaturalKey FOREIGN KEY
(
NaturalKey
) REFERENCES dbo.TableBase
(
NaturalKey
); 

-- generate fake data
INSERT  INTO dbo.TableBase
        (NaturalKey
        )
SELECT  number
FROM    master.dbo.spt_values
WHERE   number BETWEEN 1 AND 100
GROUP BY number
ORDER BY number;

INSERT  INTO dbo.TableXRef
        (NaturalKey
       , DummyData
        )
SELECT  MIN(number) AS number
      , SOUNDEX(name) AS DummyData
FROM    master.dbo.spt_values
WHERE   number BETWEEN 1 AND 100
        AND SOUNDEX(name) IS NOT NULL
GROUP BY SOUNDEX(name)
ORDER BY number;

-- destroy some data
DELETE x
FROM dbo.TableXRef x
WHERE id&gt;80

DELETE b
FROM dbo.TableBase b
LEFT JOIN dbo.TableXRef x
ON b.NaturalKey = x.NaturalKey
WHERE x.id IS NULL

Deployment script being generated, please note that the foreign key is being disabled but he unique key is supposed to be dropped, this is what is then causing the failure

/*
Run this script on:

QSQL08OCR.tempdb    -  This database will be modified

to synchronize it with:

MJ00KVMP.tempdb

You are recommended to back up your database before running this script

Script created by SQL Data Compare version 12.0.32.3340 from Red Gate Software Ltd at 10/14/2016 9:43:52 AM

*/
		
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)

PRINT(N'Drop constraints from &#91;dbo&#93;.&#91;TableXRef&#93;')
ALTER TABLE &#91;dbo&#93;.&#91;TableXRef&#93; NOCHECK CONSTRAINT &#91;FK_dbo_TableXRef_NaturalKey_dbo_TableBase_NaturalKey&#93;

PRINT(N'Drop unused indexes from &#91;dbo&#93;.&#91;TableXRef&#93;')
ALTER TABLE &#91;dbo&#93;.&#91;TableXRef&#93; DROP CONSTRAINT &#91;UQ_dbo_TableBase_DummyData&#93;

PRINT(N'Drop unused indexes from &#91;dbo&#93;.&#91;TableBase&#93;')
ALTER TABLE &#91;dbo&#93;.&#91;TableBase&#93; DROP CONSTRAINT &#91;UQ_dbo_TableBase_NaturalKey&#93;

PRINT(N'Add rows to &#91;dbo&#93;.&#91;TableBase&#93;')
SET IDENTITY_INSERT &#91;dbo&#93;.&#91;TableBase&#93; ON
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (15, '15')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (16, '16')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (17, '17')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (18, '18')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (19, '19')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (20, '20')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (21, '21')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (22, '22')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (23, '23')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (24, '24')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (25, '25')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (26, '26')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (27, '27')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (28, '28')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (29, '29')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (30, '30')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (31, '31')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (32, '32')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (33, '33')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (34, '34')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (35, '35')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (36, '36')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (37, '37')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (38, '38')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (39, '39')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (40, '40')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (41, '41')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (42, '42')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (43, '43')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (44, '44')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (45, '45')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (46, '46')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (47, '47')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (48, '48')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (49, '49')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (50, '50')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (51, '51')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (52, '52')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (53, '53')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (54, '54')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (55, '55')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (56, '56')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (57, '57')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (58, '58')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (59, '59')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (60, '60')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (61, '61')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (62, '62')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (63, '63')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (64, '64')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (65, '65')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (66, '66')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (67, '67')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (68, '68')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (69, '69')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (70, '70')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (71, '71')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (72, '72')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (73, '73')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (74, '74')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (75, '75')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (76, '76')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (77, '77')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (78, '78')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (79, '79')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (80, '80')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (81, '81')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (82, '82')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (83, '83')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (84, '84')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (85, '85')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (86, '86')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (87, '87')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (88, '88')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (89, '89')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (90, '90')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (91, '91')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (92, '92')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (93, '93')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (94, '94')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (95, '95')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (96, '96')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (97, '97')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (98, '98')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (99, '99')
INSERT INTO &#91;dbo&#93;.&#91;TableBase&#93; (&#91;id&#93;, &#91;NaturalKey&#93;) VALUES (100, '100')
SET IDENTITY_INSERT &#91;dbo&#93;.&#91;TableBase&#93; OFF
PRINT(N'Operation applied to 86 rows out of 86')

PRINT(N'Add rows to &#91;dbo&#93;.&#91;TableXRef&#93;')
SET IDENTITY_INSERT &#91;dbo&#93;.&#91;TableXRef&#93; ON
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (81, '16', 'R525')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (82, '16', 'B210')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (83, '16', 'C423')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (84, '16', 'D110')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (85, '16', 'D236')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (86, '16', 'T650')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (87, '26', 'R165')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (88, '32', 'L352')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (89, '32', 'S263')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (90, '32', 'H133')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (91, '32', 'S640')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (92, '64', 'A631')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (93, '64', 'S332')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (94, '64', 'S616')
INSERT INTO &#91;dbo&#93;.&#91;TableXRef&#93; (&#91;id&#93;, &#91;NaturalKey&#93;, &#91;DummyData&#93;) VALUES (95, '64', 'P600')
SET IDENTITY_INSERT &#91;dbo&#93;.&#91;TableXRef&#93; OFF
PRINT(N'Operation applied to 15 rows out of 15')

PRINT(N'Add indexes to &#91;dbo&#93;.&#91;TableXRef&#93;')
ALTER TABLE &#91;dbo&#93;.&#91;TableXRef&#93; ADD CONSTRAINT &#91;UQ_dbo_TableBase_DummyData&#93; UNIQUE NONCLUSTERED  (&#91;DummyData&#93;) ON &#91;PRIMARY&#93;

PRINT(N'Add indexes to &#91;dbo&#93;.&#91;TableBase&#93;')
ALTER TABLE &#91;dbo&#93;.&#91;TableBase&#93; ADD CONSTRAINT &#91;UQ_dbo_TableBase_NaturalKey&#93; UNIQUE NONCLUSTERED  (&#91;NaturalKey&#93;) ON &#91;PRIMARY&#93;

PRINT(N'Add constraints to &#91;dbo&#93;.&#91;TableXRef&#93;')
ALTER TABLE &#91;dbo&#93;.&#91;TableXRef&#93; WITH CHECK CHECK CONSTRAINT &#91;FK_dbo_TableXRef_NaturalKey_dbo_TableBase_NaturalKey&#93;
COMMIT TRANSACTION
GO

jmeyer · October 20, 2016 9:02AM

Update:

I am using options "Disable foreign keys" and "Drop primary keys, indexes, and unique constraints" enabled.

Anyway, yes, if I do not enable to option to drop/recreate indexes it will work. However, if I do enable to drop/recreate indexes then SQL DataCompare should be smart enough to then also drop/create/check the corresponding foreign keys instead of disable/enable.

And the reason I prefer to drop/create indexes is that I regularly insert/update/delete millions of records via SQL DataCompare and I do not want the indexes to be updated for every single insert/update/delete statement and potentially run into issues with unique constraints or unique indexes.

jad albert · October 21, 2016 6:41PM

jmeyer wrote:

The comparison happens on the primary key, but the foreign key is linked to a unique index.
Reproduction:
[code]-- Source System

USE tempdb;
GO

SET ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_PADDING ON;
GO

It looks complicated!

Foreign key issues

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk