diff options
Diffstat (limited to 'www/wiki/maintenance/mssql/archives/patch-archive-drop-fks.sql')
-rw-r--r-- | www/wiki/maintenance/mssql/archives/patch-archive-drop-fks.sql | 59 |
1 files changed, 59 insertions, 0 deletions
diff --git a/www/wiki/maintenance/mssql/archives/patch-archive-drop-fks.sql b/www/wiki/maintenance/mssql/archives/patch-archive-drop-fks.sql new file mode 100644 index 00000000..3055ac98 --- /dev/null +++ b/www/wiki/maintenance/mssql/archives/patch-archive-drop-fks.sql @@ -0,0 +1,59 @@ +DECLARE @base nvarchar(max), + @SQL nvarchar(max), + @id sysname;-- + +SET @base = 'ALTER TABLE /*_*/archive DROP CONSTRAINT ';-- + +SELECT @id = fk.name +FROM sys.foreign_keys fk +JOIN sys.foreign_key_columns fkc + ON fkc.constraint_object_id = fk.object_id +JOIN sys.columns c + ON c.column_id = fkc.parent_column_id + AND c.object_id = fkc.parent_object_id +WHERE + fk.parent_object_id = OBJECT_ID('/*_*/archive') + AND fk.referenced_object_id = OBJECT_ID('/*_*/revision') + AND c.name = 'ar_parent_id';-- + +SET @SQL = @base + @id;-- + +EXEC sp_executesql @SQL;-- + +-- while we're at it, let's fix up the other foreign key constraints on archive +-- as future patches touch constraints on other tables, they'll take the time to update constraint names there as well +SELECT @id = fk.name +FROM sys.foreign_keys fk +JOIN sys.foreign_key_columns fkc + ON fkc.constraint_object_id = fk.object_id +JOIN sys.columns c + ON c.column_id = fkc.parent_column_id + AND c.object_id = fkc.parent_object_id +WHERE + fk.parent_object_id = OBJECT_ID('/*_*/archive') + AND fk.referenced_object_id = OBJECT_ID('/*_*/mwuser') + AND c.name = 'ar_user';-- + +SET @SQL = @base + @id;-- + +EXEC sp_executesql @SQL;-- + +ALTER TABLE /*_*/archive ADD CONSTRAINT ar_user__user_id__fk FOREIGN KEY (ar_user) REFERENCES /*_*/mwuser(user_id);-- + +SELECT @id = fk.name +FROM sys.foreign_keys fk +JOIN sys.foreign_key_columns fkc + ON fkc.constraint_object_id = fk.object_id +JOIN sys.columns c + ON c.column_id = fkc.parent_column_id + AND c.object_id = fkc.parent_object_id +WHERE + fk.parent_object_id = OBJECT_ID('/*_*/archive') + AND fk.referenced_object_id = OBJECT_ID('/*_*/text') + AND c.name = 'ar_text_id';-- + +SET @SQL = @base + @id;-- + +EXEC sp_executesql @SQL;-- + +ALTER TABLE /*_*/archive ADD CONSTRAINT ar_text_id__old_id__fk FOREIGN KEY (ar_text_id) REFERENCES /*_*/text(old_id) ON DELETE CASCADE; |