Script tries to drop constraint that does not exist

dhsjphd · March 15, 2007 11:58AM

I am evaluating SDC 5 and am trying to move data between two servers with slight schema differences (staging --> development) and am receiving the following error:

The following error message was returned from the SQL Server:

&#91;3728&#93; 'FK_wrk_lim_funds_promos' is not a constraint.
Could not drop constraint. See previous errors.

Why is this tool creating a script to drop a constraint/FK that does not exist?
Has anyone else seen this and know of a workaround?
Is there a better way to move data between servers where foreign keys are involved?

dhsjphd · March 15, 2007 12:10PM

Update...

The constraint did exist but SDC generate the DROP command twice in the script. I saved the script then removed the duplicate statement and all was well.

richardjm · March 30, 2007 8:58AM

I'm just wondering if you are synching both a table and view on that table or something strange like that. I don't see why it would duplicate a drop constraint although we'll certainly look into it.

Darren166 · April 14, 2007 9:20AM

I have changed the structure of a large database that I had a copy of, I then applied the structure change to the latest copy of the live database which went fine, creating a lot of empty tables. I then did a data comparison to update most of the new tables, but when I run the comparison it fails for the same reason mentioned above, a lot of the constraint removal code is duplicated. The duplication seems to get greater as the code moves further down. Here is an example of the code;

-- Drop constraint FK_AWD_WebsiteBrochure_AWD_Website from &#91;dbo&#93;.&#91;AWD_WebsiteBrochure&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_WebsiteBrochure&#93; DROP CONSTRAINT &#91;FK_AWD_WebsiteBrochure_AWD_Website&#93;

-- Drop constraint FK_AWD_WebsiteBrochure_AWD_Website from &#91;dbo&#93;.&#91;AWD_WebsiteBrochure&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_WebsiteBrochure&#93; DROP CONSTRAINT &#91;FK_AWD_WebsiteBrochure_AWD_Website&#93;

-- Drop constraint FK_AWD_WebsiteBrochure_AWD_Website from &#91;dbo&#93;.&#91;AWD_WebsiteBrochure&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_WebsiteBrochure&#93; DROP CONSTRAINT &#91;FK_AWD_WebsiteBrochure_AWD_Website&#93;

-- Drop constraint FK_AWD_ProductGroups_AWD_ProductTypes from &#91;dbo&#93;.&#91;AWD_ProductGroups&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_ProductGroups&#93; DROP CONSTRAINT &#91;FK_AWD_ProductGroups_AWD_ProductTypes&#93;

-- Drop constraint FK_AWD_ProductGroups_AWD_ProductTypes from &#91;dbo&#93;.&#91;AWD_ProductGroups&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_ProductGroups&#93; DROP CONSTRAINT &#91;FK_AWD_ProductGroups_AWD_ProductTypes&#93;

-- Drop constraint FK_AWD_ProductGroups_AWD_ProductTypes from &#91;dbo&#93;.&#91;AWD_ProductGroups&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_ProductGroups&#93; DROP CONSTRAINT &#91;FK_AWD_ProductGroups_AWD_ProductTypes&#93;

-- Drop constraint FK_AWD_ProductGroups_AWD_ProductTypes from &#91;dbo&#93;.&#91;AWD_ProductGroups&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_ProductGroups&#93; DROP CONSTRAINT &#91;FK_AWD_ProductGroups_AWD_ProductTypes&#93;

-- Drop constraint FK_AWD_ProductCertifications_AWD_ProductTestingHouse from &#91;dbo&#93;.&#91;AWD_ProductGroupCertifications&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_ProductGroupCertifications&#93; DROP CONSTRAINT &#91;FK_AWD_ProductCertifications_AWD_ProductTestingHouse&#93;

-- Drop constraint FK_AWD_ProductCertifications_AWD_ProductTestingHouse from &#91;dbo&#93;.&#91;AWD_ProductGroupCertifications&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_ProductGroupCertifications&#93; DROP CONSTRAINT &#91;FK_AWD_ProductCertifications_AWD_ProductTestingHouse&#93;

-- Drop constraint FK_AWD_ProductCertifications_AWD_ProductTestingHouse from &#91;dbo&#93;.&#91;AWD_ProductGroupCertifications&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_ProductGroupCertifications&#93; DROP CONSTRAINT &#91;FK_AWD_ProductCertifications_AWD_ProductTestingHouse&#93;

-- Drop constraint FK_AWD_ProductCertifications_AWD_ProductTestingHouse from &#91;dbo&#93;.&#91;AWD_ProductGroupCertifications&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_ProductGroupCertifications&#93; DROP CONSTRAINT &#91;FK_AWD_ProductCertifications_AWD_ProductTestingHouse&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductSuppliers from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductSuppliers&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductSuppliers from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductSuppliers&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductSuppliers from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductSuppliers&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductSuppliers from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductSuppliers&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductSuppliers from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductSuppliers&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductStatus from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductStatus&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductStatus from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductStatus&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductStatus from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductStatus&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductStatus from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductStatus&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductStatus from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductStatus&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductStatus from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductStatus&#93;

-- Drop constraint FK_AWD_Products_AWD_ProductStatus from &#91;dbo&#93;.&#91;AWD_Products&#93;
ALTER TABLE &#91;dbo&#93;.&#91;AWD_Products&#93; DROP CONSTRAINT &#91;FK_AWD_Products_AWD_ProductStatus&#93;

I reverted to SQLData compare version 3 and it worked fine.

Darren

jimboh · June 15, 2007 2:17AM

I am also concerned about a solution to this.
In a current case script there were 15 cases of FK DROP CONSTRAINT statement duplication ( out of 18 needed).

Note: I am running the script in SQL Server Management Studio. The issue did not occur on prior occasions running directly from SQLDataCompare.

One also wonders whether it would be better to Disable rather than DROP and ADD Constraints.

psmolarkiewicz · August 28, 2007 1:41PM

I am also experiencing the same problem, using DataCompare 5.3.0.68. I am not doing any comparison of views, only tables. A fix would be greatly appreciated.

Robert C · August 29, 2007 3:41AM

Hi,

This has been fixed in our 6.0 branch of Data Compare, which should be released in the next couple of months.

If you want to get your hands on it earlier than that, you might like to take a look at the SDC 6 Alpha forum, as I believe the fix made it into the alpha release.

Hope that helps,
Rob

R. Schreurs · September 13, 2007 5:29AM

I have ecountered the same problem with SQL Data Compare version 5.3.0.68: numerous duplications of dropping and adding foreign key constraints in the script.

I think this is serious enough to have a hot fix for, or, alternatively, a proposal for an older version to use.

Robert C · September 13, 2007 5:34AM

Hi,

We're expecting to release SQL Data Compare 6 fairly soon - hopefully with a release candidate in the next few weeks - which should fix this bug.

Many thanks,
Robert

Script tries to drop constraint that does not exist

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk