Conditional compare/removal of foreign keys

goransgorans Posts: 2
edited May 27, 2010 7:30AM in SQL Compare Previous Versions
We have a following scenario:
- DevDB
- Tables
-- User [UserID Primary Key]
-- FactoryContact [EnteredByUserID FK to User_UserID]
-- WarehouseContact [EnteredByUserID FK to User_UserID]
(These tables of course have additional keys and columns omitted for clarity)
- ProductionModelDB - this database has all three tables as they are in production. It is a model of our production databases.

- FactoryClientDB_100
- FactoryClientDB_101
- FactoryClientDB_xxx
- WarehouseClientDB_100
- WarehouseClientDB_xxx

Each of our factory clients has two tables User and FactoryContact and each of our warehouse clients has User table and WarehouseContact tables. There are hundreds of databases for each type of client.

Here is where the problem comes in. Lets say a change is made to User table. For obvious security reasons no comparisons can be done against prod servers so it is done against DevServer.ProductionModelDB. SQL Compare will generate change scripts that will look like this

Remove FK to FactoryContact
Remove FK to WarehouseContact
Alter User table
Add FK to FactoryContact
Add FK to WarehouseContact

Of course that works great on model database because model database has both FactoryContact and WarehouseContact tables in it. In production however, those tables are conditionally generated depending on what module client has purchased. That of course causes removal of the keys to fail on one of those lines.

We have so far been doing compare by creating two model databases based on what 'modules' (as we call them - factory or warehouse) our clients have purchased. However as we are about to have half dozen or more modules that is becoming very difficult. Ideally we could disable FKs conditionally with If Exists and then create them conditionally again depending on table existence.

Of course, any other solution is welcome because we have almost 1000 objects distributed across many modules and client databases and we really cannot go client by client or even module by module.

Right now we resort to pasting If Exists in the script post facto so if that is the best solution for now please let me know.




Sign In or Register to comment.