How can we help you today? How can we help you today?

Dropping ALL constraints of a table

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?
wtay
0

Comments

4 comments

Sort by
  • 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
    Brian Donahue
    0
  • ShadowDBA
    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.
    ShadowDBA
    0
  • 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.
    Brian Donahue
    0
  • 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.
    Rosemarry
    0

Add comment

Please sign in to leave a comment.