sql-server-2008 – 如何使外键约束受信任？

我有两张桌子,销售和产品. Sale具有引用Product的外键约束.外键是使用NOCHECK创建的,并在创建后立即禁用.我想启用并信任外键约束.启用它可以工作,但我不能让它值得信任.

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

我试图将示例代码放在sql Fiddle上,但它不喜欢“DBCC”命令,所以这里是：

-- "_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_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.

解决方法

根据您的示例,我也尝试过：

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

然后我注意到命令中有一些东西：

NOT FOR REPLICATION

看起来如果使用NOT FOR REPLICATION创建约束,它始终不受信任.

引自Books Online：

In some cases,it is desirable for user activity in a replication
topology to be treated differently from agent activity. For example,
if a row is inserted by a user at the Publisher and that insert
satisfies a check constraint on the table,it might not be required to
enforce the same constraint when the row is inserted by a replication
agent at the Subscriber. The NOT FOR REPLICATION option allows you to
specify that the following database objects are treated differently
when a replication agent performs an operation:

Foreign key constraints

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影响的复制相关.我想只要在您正在处理的服务器上强制执行约束,它应该没问题.所以我继续并证实了这一点：

SELECT name,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,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.

如果你们想知道,我已经验证了对CHECK约束的相同效果.

sql-server-2008 – 如何使外键约束受信任？

解决方法

猜你在找的MsSQL相关文章