我正在向SSDT项目添加脚本,以管理静态数据表中数据的添加/更新。其中一些表相互引用,所以我需要能够在更新期间关闭约束,这样我就可以让数据处于不一致的状态,直到所有脚本都运行完(我有一个表a的文件,还有一个表B的单独文件)。
一旦完成,我需要重新启用所有约束(并让系统检查数据以确保其一致性)。需要注意的是,由于各种原因,一些约束当前被禁用,我需要确保它们恢复到脚本之前的状态。
我似乎找不到列出FK约束的当前状态的视图/表。
有没有任何方法可以让SSDT发布自动执行此操作?如果没有,有人能推荐一个好的解决方案吗?
最后,我使用了sys的组合。Foreign_Keys和sys.check_contraints,因为disable-all命令禁用了它们。在进行工作之前,我会记录约束的状态,然后在工作结束时将它们重新启用到原始状态。
If OBJECT_ID('tempdb..#tempConstraints') is not null Drop Table #tempConstraints;
GO
IF (SELECT OBJECT_ID('tempdb..#tmpScriptErrors')) IS NOT NULL DROP TABLE #tmpScriptErrors
GO
CREATE TABLE #tmpScriptErrors (Error int)
GO
Create Table #tempConstraints
(
ConstraintName nVarchar(200),
TableName nVarchar(200),
SchemaName nVarchar(200),
IsNotTrusted bit
);
GO
Begin Tran
Insert into #tempConstraints (ConstraintName, TableName, SchemaName, IsNotTrusted)
Select K.name, object_name(K.parent_object_id), SCHEMA_NAME(T.schema_id), K.Is_Not_Trusted
FROM sys.foreign_keys K
Inner Join sys.tables T on K.parent_object_id = T.object_id
Where is_disabled = 0
Union all
Select K.name, object_name(K.parent_object_id), SCHEMA_NAME(T.schema_id), K.Is_Not_Trusted
from sys.check_constraints K
Inner Join sys.tables T on K.parent_object_id = T.object_id
Where is_disabled = 0
--Disable the Constraints.
Print 'Disabling Constraints'
Exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
----------------------------------------------------------------------------------------
--Do Work Here
----------------------------------------------------------------------------------------
Declare @name nvarchar(200);
Declare @table nvarchar(200);
Declare @schema nvarchar(200);
Declare @script nvarchar(max);
Declare @NotTrusted bit;
Declare constraints_cursor CURSOR FOR
Select ConstraintName, TableName, SchemaName, IsNotTrusted
From #tempConstraints;
Open constraints_cursor;
Fetch Next from constraints_cursor
into @name, @table, @schema, @NotTrusted;
While @@FETCH_STATUS = 0
Begin
--Restore each of the Constraints back to exactly the state they were in prior to disabling.
If @NotTrusted = 1
Set @script = 'ALTER TABLE [' + @schema + '].[' + @table + '] WITH NOCHECK CHECK CONSTRAINT [' + @name + ']';
Else
Set @script = 'ALTER TABLE [' + @schema + '].[' + @table + '] WITH CHECK CHECK CONSTRAINT [' + @name + ']';
exec sp_executesql @script;
If @@ERROR <> 0
Begin
INSERT INTO #tmpScriptErrors (Error)
VALUES (1);
End
Fetch Next from constraints_cursor
into @name, @table, @schema, @NotTrusted;
End
Close constraints_cursor;
Deallocate constraints_cursor;
If exists (Select 'x' from #tmpScriptErrors)
ROLLBACK TRAN;
Else
COMMIT TRAN;
Drop table #tmpScriptErrors
GO
Drop table #tempConstraints
GO