Altering a Foreign Key Referenced Table

If you want to truncate or alter a table that is referenced by a foreign key, there are many times you are not allowed to do so. I created a script that drops the constraints, then waits for you to click an OK button, and finally adds the foreign key constraints again. It copies the SQL generated script to the clipboard as well, and also dumps intermediate results.

When I shared my original code on the LINQPad forum (www.LINQPad.net), Joe Albahari kindly picked the query up, and improved it by creating a Disposable class to perform the actions. Instead of pressing OK, the ALTER TABLE or TRUNCATE TABLE statements can be put in between the Drop and Add statements. This way the foreign key constraints would be added again even in case of an error or process termination. Joe shared his version on share.linqpad.net/b7qwds.linq. I made some slight changes and share the code with you in my blog as well here. Enjoy!

#define TRACE

void Main()
{
string tableName = “(your table name)”;

using (BounceForeignKeys (tableName))
ExecuteCommand(“SELECT ‘(Insert yout ALTER TABLE ot TRUNCATE TABLE command here)’ AS Instruction”);
}

public IDisposable BounceForeignKeys (string tableName)
{
var keys = DropForeignKeyBuilder (tableName);
return Disposable.Create (() => AddForeignKeyBuilder (keys));
}

public IList DropForeignKeyBuilder( string tableName )
{
var sql = new StringBuilder();

sql.AppendLine( @”DECLARE @error_line int” );
sql.AppendLine( @” , @error_number int” );
sql.AppendLine( @” , @error_message nvarchar(4000)” );
sql.AppendLine( @” , @error_procedure nvarchar(128)” );
sql.AppendLine( @” , @error_severity int” );
sql.AppendLine( @” , @error_state int” );
sql.AppendLine();
sql.AppendLine( @”BEGIN TRANSACTION” );
sql.AppendLine();
sql.AppendLine( @”BEGIN TRY” );

var keysReferenced = from fk in sys.foreign_keys
join rt in sys.tables on fk.referenced_object_id equals rt.object_id
join pt in sys.tables on fk.parent_object_id equals pt.object_id
where rt.name == tableName
select new KeyInfo { primary_object_id = pt.object_id,
referenced_object_id = rt.object_id,
PrimaryTableName = pt.name,
ReferencedTableName = rt.name,
fk_object_id = fk.object_id,
FkName = fk.name,
FkColumnNames = “”,
ReferencedTablePkColumnNames = “”
};
// keysReferenced.Dump( “My foreign keys” );
var keysReferenced2 = ( from k in keysReferenced
where false
select k ).ToList();

foreach ( var k in keysReferenced )
{
var foreignKeys = from fc in sys.columns
join fkc in sys.foreign_key_columns
on new { fc.object_id, fc.column_id } equals new { object_id = fkc.parent_object_id, column_id = fkc.parent_column_id }
where k.primary_object_id == fkc.parent_object_id && k.referenced_object_id == fkc.referenced_object_id && k.fk_object_id == fkc.constraint_object_id
select new { fc.name };

// foreignKeys.Dump( “My foreign key column names” );

string fkColumnNames = “”;
string sep = “”;

foreach ( var fk in foreignKeys )
{
fkColumnNames += sep + fk.name;
sep = “, “;
}

var primaryKeys = from i in sys.indexes
join ic in sys.index_columns on new { i.object_id, i.index_id } equals new { ic.object_id, ic.index_id }
join pc in sys.columns on new { ic.object_id, ic.column_id } equals new { pc.object_id, pc.column_id }
where k.referenced_object_id == i.object_id && i.is_primary_key.Value
select new { pc.name };

// primaryKeys.Dump( “Primary key column names of referenced tables” );

string pkNames = “”;
sep = “”;

foreach ( var pk in primaryKeys )
{
pkNames += sep + pk.name;
sep = “, “;
}

keysReferenced2.Add( new KeyInfo { primary_object_id = k.primary_object_id,
referenced_object_id = k.referenced_object_id,
PrimaryTableName = k.PrimaryTableName,
ReferencedTableName = k.ReferencedTableName,
fk_object_id = k.fk_object_id,
FkName = k.FkName,
FkColumnNames = fkColumnNames,
ReferencedTablePkColumnNames = pkNames } );
}

keysReferenced2.DumpT(“Foreign Keys”);

// Generate SQL
foreach ( var k in keysReferenced2 )
{
sql.AppendFormat( @” IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{0}]’) AND parent_object_id = OBJECT_ID(N'[dbo].[{1}]’)){2}” +
@” ALTER TABLE [dbo].[{1}] DROP CONSTRAINT [{0}]{2}”, k.FkName, k.PrimaryTableName, Environment.NewLine );
sql.AppendLine();
}

sql.AppendLine( @” COMMIT” );
sql.AppendLine( @” SELECT ‘Drop foreign key constrained committed’ AS [Message]” );
sql.AppendLine( @”END TRY” );
sql.AppendLine();
sql.AppendLine( @”BEGIN CATCH” );
sql.AppendLine( @” SELECT @error_line = ERROR_LINE()” );
sql.AppendLine( @” , @error_number = ERROR_NUMBER()” );
sql.AppendLine( @” , @error_message = ERROR_MESSAGE()” );
sql.AppendLine( @” , @error_procedure = ERROR_PROCEDURE()” );
sql.AppendLine( @” , @error_severity = ERROR_SEVERITY()” );
sql.AppendLine( @” , @error_state = ERROR_STATE()” );
sql.AppendLine();
sql.AppendLine( @” ROLLBACK” );
sql.AppendLine();
sql.AppendLine( @” PRINT ‘Error occurred, transaction rolled back'” );
sql.AppendLine();
sql.AppendLine( @” SELECT @error_line AS ERROR_LINE” );
sql.AppendLine( @” , @error_number AS ERROR_NUMBER” );
sql.AppendLine( @” , @error_message AS ERROR_MESSAGE” );
sql.AppendLine( @” , @error_procedure AS ERROR_PROCEDURE” );
sql.AppendLine( @” , @error_severity AS ERROR_SEVERITY” );
sql.AppendLine( @” , @error_state AS ERROR_STATE” );
sql.AppendLine( @”END CATCH” );
sql.AppendLine( @”/* GO */” );

sql.ToString().DumpT(“– DROP SQL”);

ExecuteCommand( sql.ToString() );

return keysReferenced2;
}

public void AddForeignKeyBuilder( IList keysReferenced )
{
var sql = new StringBuilder();

sql.AppendLine( @”DECLARE @error_line int” );
sql.AppendLine( @” , @error_number int” );
sql.AppendLine( @” , @error_message nvarchar(4000)” );
sql.AppendLine( @” , @error_procedure nvarchar(128)” );
sql.AppendLine( @” , @error_severity int” );
sql.AppendLine( @” , @error_state int” );
sql.AppendLine();
sql.AppendLine( @”BEGIN TRANSACTION” );
sql.AppendLine();
sql.AppendLine( @”BEGIN TRY” );

foreach ( var k in keysReferenced )
{
sql.AppendFormat( ” ALTER TABLE [dbo].[{0}] WITH CHECK ADD CONSTRAINT [{1}] FOREIGN KEY([{2}])\n” +
” REFERENCES [dbo].[{3}] ([{4}]){5}”
, new [] { k.PrimaryTableName, k.FkName, k.FkColumnNames, k.ReferencedTableName, k.ReferencedTablePkColumnNames, Environment.NewLine } );
sql.AppendLine();
sql.AppendFormat( @” ALTER TABLE [dbo].[{0}] CHECK CONSTRAINT [{1}]{2}”, k.PrimaryTableName, k.FkName, Environment.NewLine );
sql.AppendLine();
}

sql.AppendLine( @” COMMIT” );
sql.AppendLine( @” SELECT ‘Add foreign key constrained committed’ AS [Message]” );
sql.AppendLine( @”END TRY” );
sql.AppendLine();
sql.AppendLine( @”BEGIN CATCH” );
sql.AppendLine( @” SELECT @error_line = ERROR_LINE()” );
sql.AppendLine( @” , @error_number = ERROR_NUMBER()” );
sql.AppendLine( @” , @error_message = ERROR_MESSAGE()” );
sql.AppendLine( @” , @error_procedure = ERROR_PROCEDURE()” );
sql.AppendLine( @” , @error_severity = ERROR_SEVERITY()” );
sql.AppendLine( @” , @error_state = ERROR_STATE()” );
sql.AppendLine();
sql.AppendLine( @” ROLLBACK” );
sql.AppendLine();
sql.AppendLine( @” PRINT ‘Error occurred, transaction rolled back'” );
sql.AppendLine();
sql.AppendLine( @” SELECT @error_line AS ERROR_LINE” );
sql.AppendLine( @” , @error_number AS ERROR_NUMBER” );
sql.AppendLine( @” , @error_message AS ERROR_MESSAGE” );
sql.AppendLine( @” , @error_procedure AS ERROR_PROCEDURE” );
sql.AppendLine( @” , @error_severity AS ERROR_SEVERITY” );
sql.AppendLine( @” , @error_state AS ERROR_STATE” );
sql.AppendLine( @”END CATCH” );

sql.ToString().DumpT(“– ADD SQL”);

ExecuteCommand( sql.ToString() );
}

public class KeyInfo {
public int primary_object_id;
public int referenced_object_id;
public string PrimaryTableName;
public string ReferencedTableName;
public int fk_object_id;
public string FkName;
public string FkColumnNames;
public string ReferencedTablePkColumnNames;
}

class Disposable : IDisposable
{
public static IDisposable Create (Action onDispose)
{
return new Disposable (onDispose);
}

Action _onDispose;
Disposable (Action onDispose) { _onDispose = onDispose; }

public void Dispose()
{
if (_onDispose != null) _onDispose ();
_onDispose = null;
}
}

static class Extensions
{
[Conditional(“TRACE”)]
public static void DumpT (this object o, string heading = null) { o.Dump(heading); }
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s