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:
看起来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约束的相同效果. (编辑:南京站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |