Why is SQL Compare dropping constraints that don't exist?

PatrickChasco

SQL compare is generating DROP CONSTRAINT statements for default constraints which do not exist. This one is difficult to explain, so please bear with me...

I have a number of tables that are being created within a migration script. When I checked in the new tables, I did tell Red Gate that the migration script would be covering all of those tables, which it seems to have understood and is rolling them out that way rather than generating its own create table statements. So far so good.

However, during deployment Red Gate is generating drops for all default constraints on these tables immediately after running my migration script, using the wrong names. I see one of these for each table created in the script:

PRINT N'Dropping constraints from [my_schema].[table_name]'
GO
ALTER TABLE [my_schema].[table_name] DROP CONSTRAINT [DF__Revision___Seque__32B79ABA]

I tried bypassing this by adding names to each constraint rather than allowing the system to generate one. This did not make a difference.

I noticed that these constraint names are the names that were generated by my development database when I checked in the migration script. It looks like Red Gate thinks that the database I'm deploying to would be in a state where it has these constraints already, since the table definitions in the Tables folder in source control have these constraint names.

I guessed that Red Gate assumes that the migration script is creating tables that should be identical down to the constraint name as the definitions in the Tables folder. At this point I checked in the renamed constraints so that the constraint names on each table in source control exactly matches those being created in the migration script. This did not correct the problem -- it still is generating drops for those previous system-generated names.

Do I need to update my migration script so that it creates the constraints with names that match those system-generated names?

Here are the relevant parts of the deployment script being generated by Red Gate:

-- Red gate DeploymentMetadata stuff here --
...
-- End DeploymentMetadata --

-- MIGRATION SCRIPT --

PRINT N'Executing: Changes before migration script ''Create and populate revision history tables. WILL FAIL if revision tables (besides code tables) have data'''
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Finished executing: Changes before migration script ''Create and populate revision history tables. WILL FAIL if revision tables (besides code tables) have data'''
GO


PRINT N'Executing: Migration script ''Create and populate revision history tables. WILL FAIL if revision tables (besides code tables) have data'''
GO

/****** Object:  Table [revision].[ProjectCustomClass]    Script Date: 1/5/2018 9:46:24 AM ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [history].[Revision_ProjectCustomClassHistory]
(
    [Id] UNIQUEIDENTIFIER NOT NULL,
    [ProjectCustomClassId] [UNIQUEIDENTIFIER] NOT NULL,
    [Sequence] [BIGINT] NOT NULL,
    [InternalSequence] [TIMESTAMP] NOT NULL,
    [projectid] [UNIQUEIDENTIFIER] NOT NULL,
    [RevisionPacketId] [UNIQUEIDENTIFIER] NOT NULL,
    [customclasstypeid] [UNIQUEIDENTIFIER] NOT NULL,
    [customvalue] [VARCHAR](50) NOT NULL,
    [TransactionType] [CHAR](1) NOT NULL,
    [TransactionUser] [VARCHAR](100) NOT NULL,
    [TransactionApp] [VARCHAR](100) NOT NULL,
    [TransactionUtc] [DATETIME2](7) NOT NULL,
    CONSTRAINT [PK_revision_ProjectCustomClass]
        PRIMARY KEY NONCLUSTERED ([Id] ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
              ALLOW_PAGE_LOCKS = ON
             ) ON [PRIMARY]
) ON [PRIMARY];
GO


ALTER TABLE [revision].[ProjectCustomClass]
ADD [CurrentHistoryId] [UNIQUEIDENTIFIER] NOT NULL;
GO
ALTER TABLE [revision].[ProjectCustomClass] WITH CHECK
ADD CONSTRAINT [FK_revision_ProjectCustomClass_history_Revision_ProjectCustomClass]
    FOREIGN KEY ([CurrentHistoryId])
    REFERENCES [history].[Revision_ProjectCustomClassHistory] ([Id]);
GO
ALTER TABLE [revision].[ProjectCustomClass] CHECK CONSTRAINT [FK_revision_ProjectCustomClass_history_Revision_ProjectCustomClass];
GO
ALTER TABLE [history].[Revision_ProjectCustomClassHistory]
ADD CONSTRAINT [DF_Revision_ProjectCustomClassHistory_Seq]
DEFAULT (CONVERT([BIGINT], @@dbts, 0)) FOR [Sequence];
GO

ALTER TABLE [history].[Revision_ProjectCustomClassHistory]
ADD CONSTRAINT [DF_Revision_ProjectCustomClassHistory_Trans]
DEFAULT (GETUTCDATE()) FOR [TransactionUtc];
GO

CREATE UNIQUE NONCLUSTERED INDEX [UQ_Revision_ProjectCustomClass_History]
ON [history].[Revision_ProjectCustomClassHistory]
(
    [ProjectCustomClassId] ASC,
    [Sequence] DESC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF,
      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90
     ) ON [PRIMARY];
GO

-- END OF MIGRATION SCRIPT --

PRINT N'Finished executing: Migration script ''Create and populate revision history tables. WILL FAIL if revision tables (besides code tables) have data'''
GO
INSERT INTO [RedGateLocal].[DeploymentMetadata] ([Name], [Type], [Action], [BlockId], [MetadataVersion]) 
VALUES (N'Migration script ''Create and populate revision history tables. WILL FAIL if revision tables (besides code tables) have data''', 'Migration', 'Deployed', '2018-01-11-143415 9e user', '5.60.0.72')
GO

-- RED GATE DOING WEIRD STUFF --

PRINT N'Dropping constraints from [history].[Revision_ProjectCustomClassHistory]'
GO
ALTER TABLE [history].[Revision_ProjectCustomClassHistory] DROP CONSTRAINT [DF__Revision___Seque__46BE9367]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [history].[Revision_ProjectCustomClassHistory]'
GO
ALTER TABLE [history].[Revision_ProjectCustomClassHistory] DROP CONSTRAINT [DF__Revision___Trans__47B2B7A0]
GO

PatrickChasco

UPDATE

On a whim, I renamed all of the constraints on the tables to what Red Gate thought they would be, then checked in those table changes (not using a migration script). After the changes were committed, I again ran the SQL Compare tool to generate a deployment script. This time Red Gate did not generate the DROP statements for any constraints, nor did it generate create statements. I find this peculiar, since I did not change the names being given to the constraints in my migration script.