Mapping Dependencies Problem

[Simon Quin]

Is there any way to force the dependencies between database objects when creating a database from scratch from Source Control?

We have an issue on a few of our database tables where we have a default constraint that uses an user defined function to get a value. The function uses some standing data to provide the default value.

This means we have a dependency chain on the creation of the standing data table > function > table using function as a default constraint.

In our current (less than ideal) script-based solution this is no problem as we hand craft the scripts in the correct order.

I realize that this is an unusual situation, I wondered if there was any way to overcome it with a consistent automated approach?

[Brian Donahue]

Hello Simon,

The idea behind SQL Source Control is to automatically create SQL scripts from a live database. This it can do while considering dependencies.

If you wanted to create a new database from SQL files in source control managed by SQL Source Control, all that you would need to do is create a new database, then use SQL Source Control to link the new database to your repository files, and commit the changes. The new database schema will be populated in dependency-order so it matches the files in the repository.

If you want to automate this, you could create a batch script to first, check out the latest version of the files in the repository and second to use SQLCOMPARE.exe (the command-line version of SQL Compare) to synchronize the database schema with the scripts.

It may be important to note that either solution works best with scripts produced by either Red Gate tool, as custom scripts may cause dependency issues on their own.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521