Conditional compare/removal of foreign keys
gorans
Posts: 2
We have a following scenario:
DevServer
- 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.
ProductionServer
- 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.
thanks.
Goran.
DevServer
- 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.
ProductionServer
- 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.
thanks.
Goran.
Comments