Droping constraint for related table.

mohaaron

I'm having a problem where I have one table that has changed data and it's related to another table which does not have changed data. When the script is generated the tables with changed data are added into the script with the appropriate drop constaint statements. The problem that I have is that there is no drop constraint being created for the table which does not have changed data. The table with changed data is referenced by the table which does not have changed data. So the constraint from the non-changed table which points at the changed table needs to be dropped, but since the non-changed table is not included in the script the constraint is not being dropped.

How can I fix this.

mohaaron

I just made this sync work by manually dropping the relationship between the two tables, but I still wonder why SQL Data Compare doesn't see this dependency and drop/re-create the constraint on it's own.

Brian Donahue

Hello,

Thanks for asking. SQL Data Compare can potentially drop foreign keys and primary keys for the duration of a data synchronization, provided the 'disable foreign keys', 'disable primary keys', and possibly even 'disable triggers' option is set.

In older versions of Data Compare, this action would only take place on the tables being synchronized, and not in the case where the foreign key had been created on a table that is not included in the synchronization. That should have been addressed in version 5.2.

Are you using version 5.2?

Thanks!

mohaaron

Hello Brian,

I'm using version 5.2.0.40 and have the following options set.

Checked: Disable foreign keys
Checked: Drop primary keys, indexes, and unique constraints
Checked: Use transactions in SQL scripts

I do not have Disable DDL triggers checked.

Is there anything that I should change?

Brian Donahue

Hi,

Just to verify, what error message do you get when you run the synchronization script?

mohaaron

Here is the message I get, this is the first four lines of the window.

The following error message was returned from the SQL Server:

[3725] The constraint 'PK_CustomerOrder' is being referenced by table 'EmployeeCommissionShare', foreign key constraint 'FK_EmployeeCommissionShare_CustomerOrder'.
Could not drop constraint. See previous errors.

If I now remove this constraint manually and re-run the script it works.

Brian Donahue

Can you please verify that you are using both 'disable foreign keys' and 'drop primary keys and indexes' options in your project? If so, I promise to look into any possible problem with the software.

Thanks!

mohaaron

Yes, I do have both Disable foreign keys and Drop primary keys, indexes and unique constraints.

richardjm

Having a little think what may be going on here is that it's attempting to drop the primary key on your table that has changed but the foreign key of the other table uses this. This can only mean that you're not using the primary key for your table comparison of the changed table otherwise we wouldn't attempt to drop it. If you change it so that it does use the primary key for comparison then it won't attempt to drop it and all should be bunny rabbit and flowers.

This looks like a problem in the engine of SQL Data Compare. I'm surprised it has taken this long for anybody to find it.

I wonder what would happen if you unchecked the 'Drop primary keys...' option. Presumably you have it turned on for a reason.

Hope this explains things at least.

mohaaron

I found something about one of the tables that I have a question about. On one side of the comparison the PK for CustomerOrder table is not recognized. Meaning that on one side of the Tables & Views tab the Key is set to custom and on the other side the Key is automatically set to the PK CustomerOrderID. Does this have anything to do with the problem? Why would the PK be recognized on one side and not on the other.

richardjm

mohaaron wrote:

I found something about one of the tables that I have a question about. On one side of the comparison the PK for CustomerOrder table is not recognized. Meaning that on one side of the Tables & Views tab the Key is set to custom and on the other side the Key is automatically set to the PK CustomerOrderID. Does this have anything to do with the problem? Why would the PK be recognized on one side and not on the other.

Yes it would have something to do with the problem. Perhaps if you used SQL Compare to compare the schema of the tables in both databases there may be a problem with your primary key on one side. I can't think of any reason why the primary key wouldn't be picked up. Have you changed the comparison key on purpose or does PK_CustomerOrder simply not appear
in the selection list?

That perhaps is why Data Compare is attempting to drop the foreign key as it may be having problems recognising the primary key.

It's a possibility.

(If you don't have a valid license for SQL Compare and your trial has run out get in contact with me via a private message and I'll give you a trial extension for it so we can get to the bottom of this).

*curious now*

mohaaron

I think I just found something that I hadn't seen before. I was able to change to field to is used for CustomerOrder to the correct PK using a drop down in the top of the table definition popup window. I also now have left the "Drop primary key..." option un-checked and it seems to work better. I'll try some more tests now with the option checked again.