Altering table with foreign key constraint

[atolmeijer]

Hi,

When I want to change a table that has foreign key constraints, the change is not permitted. I solved the problem myself by using LINQPad (www.linqpad.net). First I drop the foreign keys, then I recreate them. I put the code I use below. Maybe you could put something like this in dbForge as well?

void Main()
{
// Generate a SQL script for manipulating a referenced table.
string tableName = "<my table name>";

// Foreign keys of table.
IList<KeyInfo> referencedKeys = DropForeignKeyBuilder( this, tableName );
MessageBox.Show( "Press OK when finished updating the table.", "Press when ready" );
AddForeignKeyBuilder( this, referencedKeys );
}

public static IList<KeyInfo> DropForeignKeyBuilder( UserQuery context, 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 context.sys.foreign_keys
join rt in context.sys.tables on fk.referenced_object_id equals rt.object_id
join pt in context.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 context.sys.columns
join fkc in context.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 context.sys.indexes
join ic in context.sys.index_columns on new { i.object_id, i.index_id } equals new { ic.object_id, ic.index_id }
join pc in context.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.Dump();

// 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 '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" );

Clipboard.SetText( sql.ToString() );
sql.ToString().Dump();

context.ExecuteCommand( sql.ToString() );

return keysReferenced2;
}

public static void AddForeignKeyBuilder( UserQuery context, IList<KeyInfo> 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" );

Clipboard.SetText( sql.ToString() );
sql.ToString().Dump();

context.ExecuteCommand( sql.ToString() );
return;
}

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;
}

[alexa]

We are aware of such problem and are planning to implement the functionality to take into account dependencies in object editors in one of the next versions of the product.