private static void buildForeignKeyScript(ForeignKey[] foreignKeyList, StringBuilder builder)
{
//ALTER TABLE [dbo].[Student] ADD
// CONSTRAINT [FK_Student_Major] FOREIGN KEY
// (
// [Major]
// ) REFERENCES [dbo].[Major] (
// [Id]
// ) ON DELETE CASCADE
//GO
foreach (var foreignKey in foreignKeyList)
{
//ALTER TABLE [dbo].[Student] WITH NOCHECK ADD
builder.AppendFormat("ALTER TABLE [dbo].[{0}] WITH NOCHECK ADD ", foreignKey.ParentTableName);
builder.AppendLine();
// CONSTRAINT [FK_Student_Major] FOREIGN KEY
builder.AppendFormat(" CONSTRAINT [{0}] FOREIGN KEY ", foreignKey.KeyName);
builder.AppendLine();
// (
builder.AppendLine(" (");
// [Major]
builder.AppendFormat(" [{0}]", foreignKey.ColumnName);
builder.AppendLine();
// ) REFERENCES [dbo].[Major] (
builder.AppendFormat(" ) REFERENCES [dbo].[{0}] (", foreignKey.ChildTableName);
builder.AppendLine();
// [Id]
builder.AppendFormat(" [{0}]", "ID");
builder.AppendLine();
// ) ON DELETE CASCADE
builder.AppendLine(" ) ");
//GO
builder.AppendLine("GO");
builder.AppendLine();
}
}
private static void builidForeignKeyIndex(ForeignKey[] foreignKeyList, StringBuilder builder)
{
//CREATE NONCLUSTERED INDEX [IX_Contract_ProjectID] ON [dbo].[Contract]
//(
// [ProjectID] ASC
//)
//GO
foreach (var foreignKey in foreignKeyList)
{
//CREATE NONCLUSTERED INDEX [IX_Contract_ProjectID] ON [dbo].[Contract]
builder.AppendFormat("CREATE NONCLUSTERED INDEX [IX_{0}_{1}] ON [dbo].[{0}] ", foreignKey.ParentTableName, foreignKey.ColumnName);
builder.AppendLine();
//(
builder.AppendLine(" (");
// [ProjectID] ASC
builder.AppendFormat(" [{0}] ASC", foreignKey.ColumnName);
builder.AppendLine();
//)
builder.AppendLine(" ) ");
//GO
builder.AppendLine("GO");
builder.AppendLine();
}
}
private static void buildForeignKeyDropScript(ForeignKey[] foreignKeyList, StringBuilder builder)
{
//if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Student_Major]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
//ALTER TABLE [dbo].[Student] DROP CONSTRAINT FK_Student_Major
//GO
foreach (var foreignKey in foreignKeyList)
{
builder.AppendFormat("if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[{0}]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)", foreignKey.KeyName);
builder.AppendLine();
builder.AppendFormat("ALTER TABLE [dbo].[{0}] DROP CONSTRAINT {1}", foreignKey.ParentTableName, foreignKey.KeyName);
builder.AppendLine();
builder.AppendLine("GO");
builder.AppendLine();
}
}
