Toad World® Forums

Build 516: Schema Comparison check constraints bug


#1

Note that the check constraint does not display in the left (Source) pane, but does in the right (Target). The generated script drops the constraint and then adds it, which is unnecessary.

Script from left pane of Schema Compare (source):

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ApplicationConfiguration] (
[Id] bit NOT NULL,
[FraudValidationIsEnabled] bit NOT NULL,
CONSTRAINT [PK_ApplicationConfiguration] PRIMARY KEY([Id]) WITH (FILLFACTOR=100) ON [PRIMARY],
CONSTRAINT [CK_ApplicationConfiguration_SingleRow] CHECK (([Id]=(1)))
) ON [PRIMARY]
GO

Script from right pane of Schema Compare (target):

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ApplicationConfiguration] (
[Id] bit NOT NULL,
[FraudValidationIsEnabled] bit NOT NULL,
CONSTRAINT [PK_ApplicationConfiguration] PRIMARY KEY([Id]) WITH (FILLFACTOR=100) ON [PRIMARY],
CONSTRAINT [CK_ApplicationConfiguration_SingleRow] CHECK (([Id]=(1)))
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ApplicationConfiguration]
WITH NOCHECK NOCHECK CONSTRAINT [CK_ApplicationConfiguration_SingleRow]
GO

Create table script from SSMS in Source:

/****** Object: Table [dbo].[ApplicationConfiguration] Script Date: 1/28/2015 12:00:15 PM ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ApplicationConfiguration](
[Id] [bit] NOT NULL,
[FraudValidationIsEnabled] [bit] NOT NULL,
CONSTRAINT [PK_ApplicationConfiguration] PRIMARY KEY CLUSTERED
(
[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 [dbo].[ApplicationConfiguration] WITH CHECK ADD CONSTRAINT [CK_ApplicationConfiguration_SingleRow] CHECK (([Id]=(1)))
GO

ALTER TABLE [dbo].[ApplicationConfiguration] CHECK CONSTRAINT [CK_ApplicationConfiguration_SingleRow]
GO

Create table script from SSMS in Source:

/****** Object: Table [dbo].[ApplicationConfiguration] Script Date: 1/28/2015 12:19:37 PM ******/

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ApplicationConfiguration](
[Id] [bit] NOT NULL,
[FraudValidationIsEnabled] [bit] NOT NULL,
CONSTRAINT [PK_ApplicationConfiguration] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[ApplicationConfiguration] WITH NOCHECK ADD CONSTRAINT [CK_ApplicationConfiguration_SingleRow] CHECK (([Id]=(1)))
GO

ALTER TABLE [dbo].[ApplicationConfiguration] NOCHECK CONSTRAINT [CK_ApplicationConfiguration_SingleRow]
GO

Script generated from Schema Compare:

/*

Script created at 1/28/2015 12:16 PM.
Please back up your database before running this script.

*/

SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF
SET ANSI_PADDING, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, XACT_ABORT ON
GO

ALTER TABLE [dbo].[ApplicationConfiguration]
DROP CONSTRAINT [CK_ApplicationConfiguration_SingleRow]
GO

ALTER TABLE [dbo].[ApplicationConfiguration]
ADD CONSTRAINT [CK_ApplicationConfiguration_SingleRow] CHECK (([Id]=(1)))
GO


#2

Hi,

Thank you very much. I create TSS-859 for this. We will discuss this issue and keep you update.

Best Regards,

Kelly