如何使外键约束可信?

本文介绍了如何使外键约束可信?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有两个表，Sale 和 Product.Sale 有一个引用 Product 的外键约束.外键是 WITH NOCHECK 创建的，并在创建后立即禁用.我想启用并信任外键约束.启用它可以工作，但我无法信任它.

I have two tables, Sale and Product. Sale has a foreign key constraint referencing Product. The foreign key was created WITH NOCHECK and immediately disabled after creation. I want to enable and trust the foreign key constraint. Enabling it works but I can't get it to be trusted.

StackOverflow 和各种博客上的类似问题表明，运行 ALTER TABLE T WITH CHECK CHECK CONSTRAINT C 应该导致 is_disabled=0 和 is_not_trusted=0，但 is_not_trusted 对我来说总是 1.我做错了什么?

Similar questions on StackOverflow and various blogs indicate that running ALTER TABLE T WITH CHECK CHECK CONSTRAINT C should result in is_disabled=0 and is_not_trusted=0, but is_not_trusted is always 1 for me. What am I doing wrong?

我尝试将示例代码放在 SQL Fiddle 上，但它不喜欢DBCC"命令，所以这里是:

I tried to put sample code on SQL Fiddle but it didn't like the "DBCC" commands, so here it is:

-- "_Scratch" is just a sandbox DB that I use for testing stuff.
USE _Scratch

CREATE TABLE dbo.Product
(
  ProductKeyId INT PRIMARY KEY NOT NULL,
  Description VARCHAR(40) NOT NULL
)

CREATE TABLE dbo.Sale
(
  ProductKeyId INT NOT NULL,
  SaleTime DATETIME NOT NULL,
  Value MONEY NOT NULL
)

ALTER TABLE dbo.Sale WITH NOCHECK
  ADD CONSTRAINT FK_Product_ProductKeyId FOREIGN KEY (ProductKeyId)
  REFERENCES dbo.Product (ProductKeyId) NOT FOR REPLICATION;

ALTER TABLE dbo.Sale NOCHECK CONSTRAINT FK_Product_ProductKeyId

INSERT INTO dbo.Product VALUES (1, 'Food')
INSERT INTO dbo.Sale VALUES (1, GETDATE(), 1.00)

-- Check the disabled/trusted state
SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Product_ProductKeyId'

  -- name                     is_disabled  is_not_trusted
  -- FK_Product_ProductKeyId  1            1

-- Check the FK_Product_ProductKeyId constraint
DBCC CHECKCONSTRAINTS('FK_Product_ProductKeyId')

  -- DBCC execution completed.
  -- If DBCC printed error messages, contact your system administrator.

-- Check all constraints on Sale table
DBCC CHECKCONSTRAINTS('Sale')

  -- DBCC execution completed.
  -- If DBCC printed error messages, contact your system administrator.

-- Add the constraint and check existing data
ALTER TABLE Sale WITH CHECK CHECK CONSTRAINT FK_Product_ProductKeyId

-- Check the disabled/trusted state
SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Product_ProductKeyId'

  -- name                     is_disabled  is_not_trusted
  -- FK_Product_ProductKeyId  0            1

-- Check the FK_Product_ProductKeyId constraint
DBCC CHECKCONSTRAINTS('FK_Product_ProductKeyId')

  -- DBCC execution completed.
  -- If DBCC printed error messages, contact your system administrator.

-- Check all constraints on Sale table
DBCC CHECKCONSTRAINTS('Sale')

  -- DBCC execution completed.
  -- If DBCC printed error messages, contact your system administrator.

推荐答案

根据你的例子，我也试过:

Based on your examples, I have also tried:

删除并重新创建外键.
删除并重新创建表.

然后我注意到命令中的一些内容:

Then I noticed something in the command:

NOT FOR REPLICATION

如果约束是用 NOT FOR REPLICATION 创建的，它似乎总是不受信任.

It seems if a constraint is created with NOT FOR REPLICATION, it is always not trusted.

引自在线图书:

在某些情况下，需要复制中的用户活动拓扑要从代理活动中区别对待.例如，如果发布者处的用户插入了一行并且该插入满足表上的检查约束，可能不需要当行被复制插入时强制执行相同的约束订阅者的代理.NOT FOR REPLICATION 选项允许您指定以下数据库对象的处理方式不同当复制代理执行操作时:

外键约束

复制代理时不强制执行外键约束执行插入、更新或删除操作.

The foreign key constraint is not enforced when a replication agent performs an insert, update, or delete operation.

看起来IS_NOT_TRUSTED 设置受IS_NOT_FOR_REPLICATION 的影响.我想只要在您正在使用的服务器上强制执行约束，就应该没问题.所以我继续确认:

It looks like the IS_NOT_TRUSTED setting is influenced by IS_NOT_FOR_REPLICATION. I guess as long as the constraint is enforced on the server you are working on, it should be fine. So I went ahead and confirmed it:

SELECT name, is_disabled, is_not_trusted
FROM sys.foreign_keys
WHERE name = 'FK_Product_ProductKeyId'

name                    is_disabled is_not_trusted
FK_Product_ProductKeyId 0            1

INSERT INTO dbo.Sale VALUES (2, GETDATE(), 1.00)

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Product_ProductKeyId". The conflict occurred in database "_Scratch", table "dbo.Product", column 'ProductKeyId'.
The statement has been terminated.

如果您仍想查看 IS_NOT_TRUSTED = 0 以确保安心，只需重新创建没有 NOT FOR REPLICATION 的外键即可.

If you still want to see IS_NOT_TRUSTED = 0 for peace of mind, just recreate the foreign key without NOT FOR REPLICATION.

如果您想知道，我也验证了对 CHECK 约束的相同效果.

In case if those of you are wondering, I have verified the same effect on CHECK constraints as well.

这篇关于如何使外键约束可信?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！