SQL Server支持禁用和启用外键约束吗?还是我唯一的选择就是放弃这些限制,然后重新创造这些限制?
当前回答
SQL-92标准允许将约束声明为DEFERRABLE,以便在事务范围内(隐式或显式)延迟它。遗憾的是,SQL Server仍然缺少这个SQL-92功能。
对我来说,将约束更改为NOCHECK类似于动态更改数据库结构——删除约束当然是这样——这是需要避免的(例如,用户需要增加特权)。
其他回答
一个脚本来管理它们:这个脚本将截断和删除命令与sp_MSforeachtable结合在一起,这样您就可以避免删除和重新创建约束——只需指定需要删除而不是截断的表,为了便于度量,我还包含了一个额外的模式过滤器(在2008r2中测试)。
declare @schema nvarchar(max) = 'and Schema_Id=Schema_id(''Value'')'
declare @deletiontables nvarchar(max) = '(''TableA'',''TableB'')'
declare @truncateclause nvarchar(max) = @schema + ' and o.Name not in ' + + @deletiontables;
declare @deleteclause nvarchar(max) = @schema + ' and o.Name in ' + @deletiontables;
exec sp_MSforeachtable 'alter table ? nocheck constraint all', @whereand=@schema
exec sp_MSforeachtable 'truncate table ?', @whereand=@truncateclause
exec sp_MSforeachtable 'delete from ?', @whereand=@deleteclause
exec sp_MSforeachtable 'alter table ? with check check constraint all', @whereand=@schema
--Drop and Recreate Foreign Key Constraints
SET NOCOUNT ON
DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)
INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'
UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME
UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME
--SELECT * FROM @table
--DROP CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
GO'
FROM
@table
--ADD CONSTRAINT:
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
GO'
FROM
@table
GO
我同意你的观点,哈姆林。当您使用SSIS传输数据或想要复制数据时,似乎很有必要暂时禁用或删除外键约束,然后重新启用或重新创建它们。在这些情况下,引用完整性不是问题,因为它已经在源数据库中进行了维护。所以,这件事你可以放心。
SQL-92标准允许将约束声明为DEFERRABLE,以便在事务范围内(隐式或显式)延迟它。遗憾的是,SQL Server仍然缺少这个SQL-92功能。
对我来说,将约束更改为NOCHECK类似于动态更改数据库结构——删除约束当然是这样——这是需要避免的(例如,用户需要增加特权)。
右键单击表设计,进入“关系”,在左侧窗格中选择外键,在右侧窗格中,将强制外键约束设置为“Yes”(启用外键约束)或“No”(禁用外键约束)。
(摘自http://www.sqljunkies.com/WebLog/roman/archive/2005/01/30/7037.aspx, 现在存档在时光倒流机中)
Foreign key constraints and check constraint are very useful for enforcing data integrity and business rules. There are certain scenarios though where it is useful to temporarily turn them off because their behavior is either not needed or could do more harm than good. I sometimes disable constraint checking on tables during data loads from external sources or when I need to script a table drop/recreate with reloading the data back into the table. I usually do it in scenarios where I don't want a time consuming process to fail because one or a few of many million rows have bad data in it. But I always turn the constraints back on once the process is finished and also in some cases I run data integrity checks on the imported data.
如果禁用外键约束,则可以插入父表中不存在的值。如果禁用检查约束,则可以将值放入列中,就像检查约束不存在一样。下面是禁用和启用表约束的一些例子:
——禁用所有表约束 修改表MyTable NOCHECK约束 ——启用所有表约束 修改MyTable,检查检查约束全部 ——禁用单个约束 修改表MyTable NOCHECK约束 ——启用单一约束 ALTER TABLE MyTable WITH CHECK CHECK CONSTRAINT MyConstraint
推荐文章
- 确定记录是否存在的最快方法
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 外键约束可能导致循环或多条级联路径?
- 如何选择每一行的列值不是独特的
- nvarchar(max)非文本
- 在SQL Server 2008 R2中重命名数据库时出错
- 将数据复制到另一个表中
- 如何在SQL中选择表的最后一条记录?
- 修改列,添加默认约束
- 在存储过程中使用“SET XACT_ABORT ON”有什么好处?
- 如何检查SQL Server文本列是否为空?
- 如何创建一个SQL Server函数“连接”多行从一个子查询到一个单独的分隔字段?