Comparison Differences between Flyway Desktop 5.15.7 and SQL Compare 14.5.22

fwin

Hi!

We are experiencing differences when trying to compare a database with a scripts folder between Flyway Desktop 5.15.7 and SQL Compare 14.5.22. We believe that these products handle foreign keys differently.

Our use case currently looks like this:

- We want to use Flyway Desktop to compare a database with a scripts folder in git
- Since we are still relying on a state-based model we want to deploy this scripts folder with SQL Change Automation
- The CI step of SQL Change Automation produces no errors (we create a BuildArtifact)
- When applying the ReleaseArtifact of SQL Change Automation we encounter foreign key constraint errors since our test database contains some old invalid data (which we do not want to delete)

Here's an example based on one of our tables. This is the output of Microsoft SQL Mangement Studio when generating a CREATE script:

```
USE [MyDatabase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[MyTable](
    [PK_MyPK] [UNIQUEIDENTIFIER] NOT NULL,
    [FK_MyFK] [UNIQUEIDENTIFIER] NOT NULL,
 CONSTRAINT [PK_MyPK] PRIMARY KEY CLUSTERED
(
    [PK_MyPK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MyTable]  WITH NOCHECK ADD  CONSTRAINT [MyConstraint] FOREIGN KEY([FK_MyFK])
REFERENCES [dbo].[OtherTable] ([PK_OtherPK])
GO

ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [MyConstraint]
GO
```

When comparing this table with an empty scripts folder, SQL Compare produces the following output:

```
CREATE TABLE [dbo].[MyTable]
(
[PK_MyPK] [uniqueidentifier] NOT NULL,
[FK_MyFK] [uniqueidentifier] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyPK] PRIMARY KEY CLUSTERED ([PK_MyPK]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD CONSTRAINT [MyConstraint] FOREIGN KEY ([FK_MyFK]) REFERENCES [dbo].[OtherTable] ([PK_OtherPK])
GO
```

And Flyway Desktop produces this output:

```
CREATE TABLE [dbo].[MyTable]
(
[PK_MyPK] [uniqueidentifier] NOT NULL,
[FK_MyFK] [uniqueidentifier] NOT NULL
)
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyPK] PRIMARY KEY CLUSTERED ([PK_MyPK])
GO
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [MyConstraint] FOREIGN KEY ([FK_MyFK]) REFERENCES [dbo].[OtherTable] ([PK_OtherPK])
GO
```

So the main difference between these scripts blocks is that Flyway Desktop does not add the `WITH NOCHECK` expression. What's also interesting is that if I change the script manually to contain `WITH NOCHECK`, Flyway Desktop does not detect any difference. It seems to me that Flyway Desktop just ignores this expression.

In summary: We believe that the comparison engine of Flyway Desktop behaves different compared to the engine of SQL Compare. Is this difference a bug or is there a way to configure Flyway Desktop so that the generated file contains `WITH NOCHECK`?

Thank you for your help!

fwin

Yesterday-me did not understand what's happening. Today-me is much smarter. The problem was that I did not adjust the comparison options in Flyway Desktop.