如何用“WITH NOCHECK”列出所有外键在SQL Server中 [英] How to list out all Foreign Keys with "WITH NOCHECK" in SQL Server

问题描述

有没有人知道列出所有外键在数据库中的WITH NOCHECK说明适用于它的查询？ （删除它们会提高性能和稳定性）。

解决方案

以下将返回当前数据库中外键的名称已禁用，即WITH NOCHECK

对于SQL Server 2005/2008：

pre $ select * from sys.foreign_keys where is_disabled = 1


b
$ b

---

关于残疾人士与残疾人士的区别，不信任。
下面的内容解释了差异
下面是一些代码，以澄清is_disabled&

$ p $ - drop table t1
- drop table t2
create table t1（i int不是null，fk int not null）
create table t2（i int not null）
- 在t2上创建主键
alter table t2
添加约束pk_1主键（i ）
- 在t1上创建foriegn键
alter table t1
添加约束fk_1外键（fk）
引用t2（i）
- 插入一些记录
插入t2值（100）
插入t2值（200）
插入t2值（300）
插入t2值（400）
插入t2值（500）
插入t1值（1,100）
插入t1值（2,100）
插入t1值（3,500）
插入t1值（4,500）
------- ---------------------
- 1.启用且可信
从sys.foreign_keys
中选择名称，is_disabled，is_not_trusted GO
$ b - 2.禁用约束
alter table t1 NOCHECK CONSTRAINT fk_1
从sys.foreign_keys中选择名称，is_disabled，is_not_trusted
GO

- 3.重新启用约束，数据没有被检查，所以不被信任。
- 这意味着优化器仍然需要检查列
alter table t1 CHECK CONSTRAINT fk_1
从sys.foreign_keys中选择名称，is_disabled，is_not_trusted
GO

--4。放下外键约束&重新添加
- 确保它的已检查的
- 约束被启用并且被信任
alter table t1 DROP CONSTRAINT fk_1
alter table t1 WITH CHECK
add约束fk_1外键（fk）
引用t2（i）
从sys.foreign_keys中选择名称，is_disabled，is_not_trusted
GO


- 5 。放下外键约束&添加但不检查
- 然后启用约束但不可信
alter table t1 DROP CONSTRAINT fk_1
alter table t1 WITH NOCHECK
添加约束fk_1外键（fk）
引用t2（i）
从sys.foreign_keys中选择名称，is_disabled，is_not_trusted
GO


is_disabled 表示限制被禁用

isnottrusted 意味着SQL Server不相信已经对外键表检查了列。

因此不能假定重新启用外键约束将被优化。为了确保优化器信任该列，最好放弃外键约束&使用 WITH CHECK 选项（4）重新创建它

Does anyone know a query for listing out all foreign keys in a database with "WITH NOCHECK" description applied to it? (removing them will boost performance and stability).

解决方案

The following will return the name of the foreign keys in the current database that are disabled i.e. WITH NOCHECK

For SQL Server 2005/2008:

select * from sys.foreign_keys where is_disabled=1

---

There was some discussion in the answer about the difference between disabled & not trusted. What's below explains the differnce Here's some code to clarify the difference between is_disabled & isnotrusted.

-- drop table t1
-- drop table t2
create table t1(i int not null, fk int not null)
create table t2(i int not null)
-- create primary key on t2
alter table t2
add constraint pk_1 primary key (i)
-- create foriegn key on t1
alter table t1
add constraint fk_1 foreign key (fk)
    references t2 (i)
--insert some records
insert t2 values(100)
insert t2 values(200)
insert t2 values(300)
insert t2 values(400)
insert t2 values(500)
insert t1 values(1,100)
insert t1 values(2,100)
insert t1 values(3,500)
insert t1 values(4,500)
----------------------------
-- 1. enabled and trusted
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

-- 2. disable the constraint
alter table t1 NOCHECK CONSTRAINT fk_1
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

-- 3. re-enable constraint, data isnt checked, so not trusted.
-- this means the optimizer will still have to check the column
alter table  t1 CHECK CONSTRAINT fk_1 
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

--4. drop the foreign key constraint & re-add 
-- it making sure its checked
-- constraint is then enabled and trusted
alter table t1  DROP CONSTRAINT fk_1
alter table t1 WITH CHECK 
add constraint fk_1 foreign key (fk)
    references t2 (i)
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO


--5. drop the foreign key constraint & add but dont check
-- constraint is then enabled, but not trusted
alter table t1  DROP CONSTRAINT fk_1
alter table t1 WITH NOCHECK 
add constraint fk_1 foreign key (fk)
    references t2 (i)
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

is_disabled means the constraint is disabled

isnottrusted means that SQL Server does not trust that the column has been checked against the foreign key table.

Thus it cannot be assumed that re-enabling the foreign key constraint will be optimized. To ensure the optimizer trusts the column, it's best to drop the foreign key constraint & re-create it with the WITH CHECK option (4.)

这篇关于如何用“WITH NOCHECK”列出所有外键在SQL Server中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！