Red Gate forums :: View topic - Updating foreign keys on non-selected tables
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare Previous Versions
SQL Compare Previous Versions forum

Updating foreign keys on non-selected tables

Search in SQL Compare Previous Versions forum
Post new topic   Reply to topic
Jump to:  
Author Message
wapodaca



Joined: 29 Mar 2011
Posts: 2

PostPosted: Wed Mar 30, 2011 2:39 pm    Post subject: Updating foreign keys on non-selected tables Reply with quote

Here's the situation. I am having the same issue inSQL Compare 8.0, 8.5, and 9.
I'm trying to update a data structure that contains live data. I am attempting to add a single table which has no foreign keys. There is however another table that has a foreign key to the table I'm adding, but only in the new data structure. That second table is NOT selected. Update Dependencies is unchecked. When SQL Compare generates the sql script it puts in the Create for the first table but also adds the alter for the second table. This is unworkable because in a live date situation I need to be able to add the new table. Run an initialize script to load the values to it. Then update the second table so that it has the foreign key.
Am I missing a setting somewhere?
Back to top
View user's profile Send private message
thirster42



Joined: 30 Nov 2010
Posts: 21

PostPosted: Mon May 09, 2011 9:55 pm    Post subject: Reply with quote

i don't see why you don't just modify the script that sql compare spits out to do what you need to do.
Back to top
View user's profile Send private message
vincentj



Joined: 17 Feb 2011
Posts: 36

PostPosted: Fri May 27, 2011 3:46 pm    Post subject: Reply with quote

I'm running into a similar issue. I'm trying to automate my deployment using SQL Compare and SQL Data Compare. It looks like I need to modify my build script to run in the following order:

1. Run SQL Compare and add new tables only
2. Run SQL Data Compare to populate the new table
3. Run SQL Compare again to sync the rest of the schema

This seems a bit cumbersome, but I guess the only way it'd be more streamlined would be if SQL Compare and SQL Data Compare were combined into a single application so it could determine such dependencies.

Does Red Gate suggest any best practices for this scenario?
Back to top
View user's profile Send private message
vincentj



Joined: 17 Feb 2011
Posts: 36

PostPosted: Mon Jun 13, 2011 11:00 pm    Post subject: Reply with quote

I finally rewrote my deployment scripts as I mentioned above. I ran into one other issue along the way, and I figured I'd document my efforts here in case anyone else finds it useful.

When I chose to sync just tables (excluding foreign keys), I got this error:

Quote:
Msg 3723, Level 16, State 6, Server MACHINE, Line 1
An explicit DROP INDEX is not allowed on index 'dbo.table1.pk_table1'. It is being used for FOREIGN KEY constraint enforcement.


It's trying to remove an index on a table, but that index can't be removed because it's referenced by a foreign key. But since I've told it to ignore foreign keys, the script doesn't include the necessary DROP CONSTRAINT. (As a side note, the release notes for SQL Compare 9 say this has been fixed, but I'm using 9.0.0.79 and I still saw this error.) I tried adding IncludeDependencies to the options, as well as Default (based on feedback from another thread, heh) and IgnoreIndexes (hoping to push the whole mess off until the tables were synced) but none of those worked.

I managed to work around it by adding two more steps to my deployment script, here's what I eventually came up with:


1. Sync NEW tables only, excluding foreign key constraints. We need most of the default options except IncludeDependencies.
Code:
SqlCompare.exe /options:DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder,IgnoreKeys /include:Table /exclude:additional /exclude:different /exclude:identical ...


2. Sync data for all tables. This will include the tables we just added so we will be able to enforce the foreign key constraints on those new tables.
Code:
SqlDataCompare.exe ... (no special options here)


3. Sync ALL tables, this time we can include foreign keys because the new tables have already been added and populated. Again, we use the default options except IncludeDependencies.
Code:
SqlCompare.exe /options:DecryptPost2KEncryptedObjects,IgnoreFillFactor,IgnoreWhiteSpace,IgnoreFileGroups,IgnoreUserProperties,IgnoreWithElementOrder /include:Table ...


4. Sync data for all tables again. This is unlikely to add anything new, unless we've added a new column to an existing static data table.
Code:
SqlDataCompare.exe ... (no special options here)


5. Sync the rest of the database objects.
Code:
SqlCompare.exe ... (no special options here)



This could still fail if you need to add a new primary key field to an existing static data table, populate that field, and then enforce a foreign key constraint against the new field. But that is a very unlikely scenario, so I'm considering this good enough for our purposes.

It still seems way more complicated than it needs to be, and I can't imagine I'm the first person to do this... do everyone's build scripts look like this, or am I just missing something?

(Edit: updated the options for steps 1 and 3; it was dropping and recreating all the indexes with the defaults excluded.)
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group