Toad World® Forums

Schema Compare - Bug


#1

We have a foreign key on a table that in the target environment had no specification for DELETE CASCADE or UPDATE CASCADE. However, our source environment is attempting to introduce this change. When Toad’s Schema Compare tool generates script to resolve this difference it does so by executing an ALTER TABLE [xyz] ADD CONSTRAINT [abc]. This produces an error as the FK name already exists. The key either needs deleted before recreated or an ALTER on the constraint itself.


#2

some updated info on this issue. There is actually a block in the script to drop the constraint, but there are two different blocks attempting to ADD the constraint back.


#3

Hi,

Sorry I can not reproduce this issue. As I try, there only one blocks attempting to add the constraint. Would you please provide the Source table and Targe table script?

Below is my synchronize script:

USE [SCB]

GO

/*

Script created at 7/9/2015 11:00 AM.

Please back up your database before running this script.

Source server: 10.30.180.161

Source database: SCA

Target server: 10.30.180.161

Target database: SCB

*/

SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF

GO

SET ANSI_PADDING, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, XACT_ABORT ON

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

GO

ALTER TABLE [dbo].[FK]

DROP CONSTRAINT [FK_TEST]

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

ALTER TABLE [dbo].[FK]

ADD CONSTRAINT [FK_TEST] FOREIGN KEY ([Column_2])

REFERENCES [dbo].[PK] ([Column_2])

ON DELETE CASCADE ON UPDATE CASCADE

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

IF @@TRANCOUNT>0 COMMIT TRANSACTION

GO

SET NOEXEC OFF

Best Regards,

Kelly

Email: kelly.chen@quest.com


#4

We reset our target environment to before this sync and ran the compare again. Once again, there were two “ADD CONSTRAINT” statements added which causes the 2nd execution to fail.

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

ALTER TABLE [dbo].[PhoneSource]

ADD CONSTRAINT [FK_dbo.PhoneSource_dbo.PhoneNumber_PhoneNumber_Id] FOREIGN KEY ([PhoneNumberId])

REFERENCES [dbo].[PhoneNumber] ([Id])

ON DELETE CASCADE ON UPDATE CASCADE

GO

Then around 150 lines later…

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

ALTER TABLE [dbo].[PhoneSource]

ADD CONSTRAINT [FK_dbo.PhoneSource_dbo.PhoneNumber_PhoneNumber_Id] FOREIGN KEY ([PhoneNumberId])

REFERENCES [dbo].[PhoneNumber] ([Id])

ON DELETE CASCADE ON UPDATE CASCADE

GO

I’m not sure what scenario may be causing the duplication, but it’s definitely present in our system.


#5

This error is still ongoing in the current Beta. Again, here is the script generated for when I only selected this single table to compare.

USE [x]

GO

/*

Script created at 10/1/2015 9:46 AM.

Please back up your database before running this script.

Source server: TEST

Source database: x

Target server: PROD

Target database: x

*/

SET NUMERIC_ROUNDABORT, IMPLICIT_TRANSACTIONS OFF

GO

SET ANSI_PADDING, ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, XACT_ABORT ON

GO

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION

GO

ALTER TABLE [rogue_id].[RogueIdPhoneSource]

DROP CONSTRAINT [FK_rogue_id.RogueIdPhoneSource_rogue_id.RogueIdPhoneNumber_RogueIdPhoneNumber_Id]

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

DROP INDEX [IX_RogueIdPhoneNumber_Id] ON [rogue_id].[RogueIdPhoneSource]

GO

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

ALTER TABLE [rogue_id].[RogueIdPhoneSource]

DROP COLUMN [RogueIdPhoneNumber_Id]

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

ALTER TABLE [rogue_id].[RogueIdPhoneSource]

ADD CONSTRAINT [FK_rogue_id.RogueIdPhoneSource_rogue_id.RogueIdPhoneNumber_RogueIdPhoneNumber_Id] FOREIGN KEY ([RoguePhoneNumberId])

REFERENCES [rogue_id].[RogueIdPhoneNumber] ([Id])

ON DELETE CASCADE ON UPDATE CASCADE

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

ALTER TABLE [rogue_id].[RogueIdPhoneSource]

ADD CONSTRAINT [FK_rogue_id.RogueIdPhoneSource_rogue_id.RogueIdPhoneNumber_RogueIdPhoneNumber_Id] FOREIGN KEY ([RoguePhoneNumberId])

REFERENCES [rogue_id].[RogueIdPhoneNumber] ([Id])

ON DELETE CASCADE ON UPDATE CASCADE

GO

IF @@ERROR <> 0 BEGIN IF @@TRANCOUNT > 0 ROLLBACK SET NOEXEC ON END

GO

IF @@TRANCOUNT>0 COMMIT TRANSACTION

GO

SET NOEXEC OFF


#6

ToadSchemaCompareDupConstraint.png

Here is what changes triggered the script


#7

Hi,

Thank you so much for your detail. I have reproduce this issue and create TSS-1206 to log it.

We will let you know if this have any update.

Thank you,

Kelly


#8

Hi,

This issue has been fixed. Please try in next Beta.

Kelly


#9

Thank you!