Foreign key issues

jmeyerjmeyer Posts: 70 Bronze 2
edited October 21, 2016 6:41PM in SQL Data Compare
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

Comments

  • 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!
    Andrew Pierce
    Technical Sales Engineer
    Redgate Software
  • jmeyerjmeyer Posts: 70 Bronze 2
    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'[dbo].[TableXRef]')
                        AND type IN (N'U') )
      DROP TABLE dbo.TableXRef;
    IF EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   object_id = OBJECT_ID(N'[dbo].[TableBase]')
                        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 [PRIMARY]
                              , CONSTRAINT UQ_dbo_TableBase_NaturalKey UNIQUE NONCLUSTERED (NaturalKey ASC) ON [PRIMARY])
    ON
      [PRIMARY];
    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 [PRIMARY]
                              , CONSTRAINT UQ_dbo_TableBase_DummyData UNIQUE NONCLUSTERED (DummyData ASC) ON [PRIMARY])
    ON
      [PRIMARY];
    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'[dbo].[TableXRef]')
                        AND type IN (N'U') )
      DROP TABLE dbo.TableXRef;
    IF EXISTS ( SELECT  1
                FROM    sys.objects
                WHERE   object_id = OBJECT_ID(N'[dbo].[TableBase]')
                        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 [PRIMARY]
                              , CONSTRAINT UQ_dbo_TableBase_NaturalKey UNIQUE NONCLUSTERED (NaturalKey ASC) ON [PRIMARY])
    ON
      [PRIMARY];
    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 [PRIMARY]
                              , CONSTRAINT UQ_dbo_TableBase_DummyData UNIQUE NONCLUSTERED (DummyData ASC) ON [PRIMARY])
    ON
      [PRIMARY];
    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>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 [dbo].[TableXRef]')
    ALTER TABLE [dbo].[TableXRef] NOCHECK CONSTRAINT [FK_dbo_TableXRef_NaturalKey_dbo_TableBase_NaturalKey]
    
    PRINT(N'Drop unused indexes from [dbo].[TableXRef]')
    ALTER TABLE [dbo].[TableXRef] DROP CONSTRAINT [UQ_dbo_TableBase_DummyData]
    
    PRINT(N'Drop unused indexes from [dbo].[TableBase]')
    ALTER TABLE [dbo].[TableBase] DROP CONSTRAINT [UQ_dbo_TableBase_NaturalKey]
    
    PRINT(N'Add rows to [dbo].[TableBase]')
    SET IDENTITY_INSERT [dbo].[TableBase] ON
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (15, '15')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (16, '16')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (17, '17')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (18, '18')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (19, '19')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (20, '20')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (21, '21')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (22, '22')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (23, '23')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (24, '24')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (25, '25')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (26, '26')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (27, '27')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (28, '28')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (29, '29')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (30, '30')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (31, '31')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (32, '32')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (33, '33')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (34, '34')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (35, '35')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (36, '36')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (37, '37')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (38, '38')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (39, '39')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (40, '40')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (41, '41')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (42, '42')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (43, '43')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (44, '44')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (45, '45')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (46, '46')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (47, '47')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (48, '48')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (49, '49')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (50, '50')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (51, '51')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (52, '52')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (53, '53')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (54, '54')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (55, '55')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (56, '56')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (57, '57')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (58, '58')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (59, '59')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (60, '60')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (61, '61')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (62, '62')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (63, '63')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (64, '64')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (65, '65')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (66, '66')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (67, '67')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (68, '68')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (69, '69')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (70, '70')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (71, '71')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (72, '72')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (73, '73')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (74, '74')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (75, '75')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (76, '76')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (77, '77')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (78, '78')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (79, '79')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (80, '80')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (81, '81')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (82, '82')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (83, '83')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (84, '84')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (85, '85')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (86, '86')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (87, '87')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (88, '88')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (89, '89')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (90, '90')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (91, '91')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (92, '92')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (93, '93')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (94, '94')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (95, '95')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (96, '96')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (97, '97')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (98, '98')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (99, '99')
    INSERT INTO [dbo].[TableBase] ([id], [NaturalKey]) VALUES (100, '100')
    SET IDENTITY_INSERT [dbo].[TableBase] OFF
    PRINT(N'Operation applied to 86 rows out of 86')
    
    PRINT(N'Add rows to [dbo].[TableXRef]')
    SET IDENTITY_INSERT [dbo].[TableXRef] ON
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (81, '16', 'R525')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (82, '16', 'B210')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (83, '16', 'C423')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (84, '16', 'D110')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (85, '16', 'D236')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (86, '16', 'T650')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (87, '26', 'R165')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (88, '32', 'L352')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (89, '32', 'S263')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (90, '32', 'H133')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (91, '32', 'S640')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (92, '64', 'A631')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (93, '64', 'S332')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (94, '64', 'S616')
    INSERT INTO [dbo].[TableXRef] ([id], [NaturalKey], [DummyData]) VALUES (95, '64', 'P600')
    SET IDENTITY_INSERT [dbo].[TableXRef] OFF
    PRINT(N'Operation applied to 15 rows out of 15')
    
    PRINT(N'Add indexes to [dbo].[TableXRef]')
    ALTER TABLE [dbo].[TableXRef] ADD CONSTRAINT [UQ_dbo_TableBase_DummyData] UNIQUE NONCLUSTERED  ([DummyData]) ON [PRIMARY]
    
    PRINT(N'Add indexes to [dbo].[TableBase]')
    ALTER TABLE [dbo].[TableBase] ADD CONSTRAINT [UQ_dbo_TableBase_NaturalKey] UNIQUE NONCLUSTERED  ([NaturalKey]) ON [PRIMARY]
    
    PRINT(N'Add constraints to [dbo].[TableXRef]')
    ALTER TABLE [dbo].[TableXRef] WITH CHECK CHECK CONSTRAINT [FK_dbo_TableXRef_NaturalKey_dbo_TableBase_NaturalKey]
    COMMIT TRANSACTION
    GO
    
  • jmeyerjmeyer Posts: 70 Bronze 2
    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.
  • 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!
Sign In or Register to comment.