Dropping ALL constraints of a table

wtay

I'm using RedgateDataCompare API v. 7.1.0.230.

I have the following two tables:
1. Enumeration (EnumerationID is the PK, ParentID references EnumerationID)
2. Supplier (SupplierID is the PK; SupplierType references EnumerationID).

The Enumeration table has the following foreign key constraints:
1. FK_Enumeration_Enumeration
2. FK_Supplier_Enumeration

When I ran a data compare on the Enumeration table with the following options:
DBOptions = IgnoreFillFactor,IgnoreWhiteSpace,IgnoreExtendedProperties,NoSQLPlumbing,IncludeDependencies,IgnoreFileGroups,IgnoreStatistics,DoNotOutputCommentHeader,IgnoreUserProperties,DisableAndReenableDdlTriggers,IgnoreWithElementOrder
DataOptions = DisableTriggers,DisableKeys,DropConstraintsAndIndexes,DDLTriggerDisable

I see that the following statements were generated:

ALTER TABLE [dbo].[Enumeration] DROP CONSTRAINT [FK_Enumeration_Enumeration]
...
INSERT INTO ENUMERATION(.....)
...
ALTER TABLE [dbo].[Enumeration] DROP CONSTRAINT [FK_Enumeration_Enumeration]


QUESTION: WHY doesn't Redgate generate statements to DROP and then ADD the FK_Supplier_Enumeration constraint??? I need to drop/add that constraint... Is there an option in Redgate to do that?

Brian Donahue

Hello,

I think that v71. of the Data Compare Engine had a bug that prevented it from dropping all foreign keys if the foreign keys were placed on a table that was not being compared. At any rate, the behavior doesn't seem right and I would suggest upgrading to the latest SDK:

http://www.red-gate.com/MessageBoard/vi ... php?t=9911

ShadowDBA

Brian Donahue wrote:

Hello,

I think that v71. of the Data Compare Engine had a bug that prevented it from dropping all foreign keys if the foreign keys were placed on a table that was not being compared. At any rate, the behavior doesn't seem right and I would suggest upgrading to the latest SDK:

http://www.red-gate.com/MessageBoard/vi ... php?t=9911

So by installing the SQL Comparison SDK 8 Latest Cumulative Patch, I can re-run my SQL Data Compare 7.1 job and it will now actually disable all foreign keys and constraints?

Or does that patch only address issues when using the API and not the SQL Data Comparisno product?

My data comparison job runs for 8 hours and then gives me messages like :

[2627] Violation of PRIMARY KEY constraint 'dbo.event_watch_column_mapping.PK_event_watch_column_mapping.pkey.sql'. Cannot insert duplicate key in object 'dbo.event_watch_column_mapping'.

Also the keys are the same in the source and destination databases, so how are we getting data that would cause this error. And wouldn't this error be generated on re-creating the primary since it is selected to be dropped durring the sync?

In synch behavior I have the following options checked:
Disable Foreign Keys
Drop primary keys, indexes, and nique constraints
use transactions in sql scripts
disable dml triggers
disable ddl triggers
Reseed identity columns

Any help you could provide would be great.

Brian Donahue

The new components should work in the SDK as well as SQL Data Compare commercial. However, you cannot "patch" v7 to v8. You would have to get an upgrade and either use an entirely new version or create new references in your v7 SDK project to the v8 dlls.

Rosemarry

Yes here's a simple scriptlet I've included in database upgrade scripts to drop all constraints on a table which will then allow dropping the table. Of course, you'll want to replace the @database and @table parameters.