Quite a while ago I wrote an article on continuous database integration. It involved scripting out a database and then using the scripts each iteration to either generate from scratch, or migrate an existing database. At the time of writing, it all worked perfectly, but recently, we've run into some problems with foreign key constraints. Basically, they now require us to run the table scripts in a particular order - something I'm not that keen on doing anymore.

Initially, we decided to just do away with these constraints, as we considered that they slow performance. But - and hindsight is 20/20 - we probably shouldn't have, and we've had to do some serious data cleansing to resolve some referential integrity issues. Realistically, we were only using the performance issue as justification for deciding not to undertake the (we thought) difficult task of working out how to make the foreign key constraints work properly. Turns out, it wasn't that difficult in the end anyway.

The first thing we need to do is to script all the foreign key constraints out to a text file. I've used wsf scripts and SQLDMO, but you could use scptxfr.exe to generate the files differently - thanks Jamie! The main reason I've continued to use the wsf files instead of migrating everything across to scptxfr.exe is because I like the extra control I have using the SQLDMO objects. Or - you could just use Enterprise Manager to do it manually. Personally, at BottleIT, we tend to steer clear of Enterprise Mangler because it makes it easier to integrate command line stuff into our continuous builds.

serverName = "(local)";databaseName = "Northwind";outputPath = "c:buildNorthwindCreate ScriptsConstraints";

server = connectServer(serverName);database = getDatabase(server, databaseName);

tableCollection = new Enumerator(database.Tables);

for( ; !tableCollection.atEnd() ; 

tableCollection.moveNext())

{

	table = tableCollection.item();

	if(!table.SystemObject)

	{	

		WScript.Echo(“Processing “ + table.Name);

		var script = scriptForeignKeys(table);

		if(script.length > 10)

		{

			var file = fso.CreateTextFile(fso.BuildPath(outputPath, table.Name + “.sql”));

			file.Write(script);

			file.Close();

		}

	}

}

server.Disconnect();

function connectServer(serverName)

{

	server = new ActiveXObject(“SQLDMO.SQLServer”);

	server.LoginSecure = true;	server.Connect(serverName, null, null);

	return server;

}

function getDatabase(server, databaseName)

{

	database = new ActiveXObject(“SQLDMO.Database”);

	database = server.Databases(databaseName);

	return database;

 }

function scriptForeignKeys(table)

{

	var params = SQLDMOScript_DRI_ForeignKeys;

	return table.Script(params, null, null, SQLDMOScript2_NoCollation);

}

This ensures that we won't lose anything.
Now we can drop all the foreign key constraints....

DECLARE tableConstraints SCROLL CURSOR FOR

	SELECT object_name(parent_obj) as constraintTable, name as constraintName

	FROM sysobjects

	WHERE xtype = 'F'           

	    AND object_name(parent_obj) IN ( SELECT name FROM sysobjects WHERE type='U')

DECLARE @tableName AS NVARCHAR(128)

DECLARE @constraintName AS NVARCHAR(128)

OPEN tableConstraints

FETCH NEXT FROM tableConstraints INTO @tableName, @constraintName

WHILE (@@FETCH_STATUS = 0)

BEGIN

	EXECUTE ('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @constraintName)

	FETCH NEXT FROM tableConstraints INTO @tableName, @constraintName

END

CLOSE tableConstraints

DEALLOCATE tableConstraints

Voila - no more constraints in the database.

Now that we don't have any constraints, we can drop and recreate our tables, and the order of creation really doesn't affect us. When we've finished, we can just iterate over the constraint files in the outputPath (c:\build\Northwind\Create Scripts\Constraints) in the above example, and our declarative referential integrity constraints magically reappear.

So the steps again...

1. Script the existing constraints to files
2. Drop all the constraints
3. Perform DDL stuff
4. Recreate constraints from the files.

The reason I'm doing it this way, is that our continuous database integration server at BottleIT does it all for us. At the start of each test suite, we rebuild the database from scratch, so we wanted to try and reduce the need to have the scripts running in a specific order, whilst keeping the requirement of having foreign keys enforced at the database level.