Bug When Dropping A PK And Making The Column Null?

Xander

Hi,
I've found an interesting deployment bug.
The source DB has a table with no primary key. In the target DB, the same table has a primary key column, and it's a column which can be null in the source DB. So during deployment, it should drop the PK constraint, then make the column nullable.
It does these 2 actions, but then proceeds to try to re-add the PK constraint that was only on the target DB. This obviously fails because the column is now nullable.
Admittedly dropping a PK during a deployment isn't ideal regardless, but I still think this is probably a bug.
Thanks.

Alex B

Hi @Xander,

What version of SQL Compare are you trying this with?  I've just checked with the latest 13.7.6 and it seems to work correctly for me.

My test was comparing a source of:

CREATE TABLE Table_1
(
id INT NULL,
asdf VARCHAR(10) NOT NULL,
other VARCHAR(5) NULL
);

with a target of:

CREATE TABLE Table_1
(
id INT PRIMARY KEY NOT NULL,
asdf VARCHAR(10) NOT NULL,
other VARCHAR(5) NULL
);

and the script that was generated was this:

BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[Table_1]'
GO
ALTER TABLE [dbo].[Table_1] DROP CONSTRAINT [PK__Table_1__3213E83FB18E33F8]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[Table_1]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[Table_1] ALTER COLUMN [id] [int] NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION

If you are using the latest version are you using any specific non-default options?

Kind regards,
Alex

Xander

Hi @Alex B,

This was version 13.7.3.9483. I've just replicated it with a similar test table, using a varchar(15) instead of an int for the primary key column:

BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping constraints from [dbo].[TestTable]'
GO
ALTER TABLE [dbo].[TestTable] DROP CONSTRAINT [PK_TestTable]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[TestTable]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
ALTER TABLE [dbo].[TestTable] ALTER COLUMN [EntryCode] [varchar] (15) NULL
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating primary key [PK_TestTable] on [dbo].[TestTable]'
GO
ALTER TABLE [dbo].[TestTable] ADD CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED  ([EntryCode])
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION

I've just done a check for updates but SQL Compare thinks it is up to date already.

Kind Regards,
Xander

Alex B

Hi @Xander,

Can you share the DDL for the source and target?  I changed my example to be VARCHAR(15) and also named the PK to be PK_TestTable, but it's still just dropping the PK and altering the column to be NULL.  I restored the Redgate Defaults for the comparison options too - have you changed any of these?

Also, if you have 13.7.3, then you may not have enabled Frequent Updates; 13.7.3 is the last "stable" release and 13.7.6 is the latest Frequent Release.

Kind regards,
Alex

Xander

Hi @Alex B

Ah, I've just figured it out. Ignore indexes was the problem, since obviously a primary key is an index. Perhaps a warning is needed when ignoring indexes is going to generate a script that can't complete?

Thanks for the help!

Alex B

Hi @Xander,

Ah, that would make sense!

Hmm, I'm not sure how that would work - we obviously know about the indexes since we're dropping and recreating it, so I suppose it should be possible to warn around that if we're making the column that is in the constraint nullable.

I'll pose this to the development team about it and see what they say, but I'm glad you've been able to get it deployed!

Kind regards,
Alex

Alex B

Hi @Xander,

It looks like you should be able to use the "Ignore performance indexes" which won't ignore PK and UQ so that this type of a thing doesn't occur (if you need to ignore other indexes).

As for warnings, the team try to avoid adding additional warnings as there has been feedback of "warning fatigue" due to the number already generated and people missing important ones.

Thanks for your feedback though!

Kind regards,
Alex