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