为什么这个T-SQL OUTPUT INTO FOREIGN KEY hack工作？ [英] Why does this T-SQL OUTPUT INTO with FOREIGN KEY hack work?

问题描述

基本示例从没有办法使用正常的外键约束的TSQL输出？他的代码按预期失败。但是如果在示例中定义约束的方式如下修改，则定义FK约束 WITH NOCHECK ，然后 CHECK

这似乎与 OUTPUT子句文档。具体地说：

lockquote
output_table [接收INTO的表]不能：

已经在其上定义了触发器。

•参与FOREIGN KEY约束的任何一方 [emphasis added]。

•有CHECK约束或启用的规则。

从关系的角度来看，下面的可以工作，但是这个行为应该被明确排除。如果FK被定义为一个直的WITH CHECK（缺省值），它将按预期失败。如果它被定义为WITH NOCHECK，然后启用CHECK CONSTRAINT，那么它就不会失败。

如果这是一个已知的，支持的功能。还是我刚刚发现SQL至少从SQL 2008（我与2008年和2014年测试）持续存在的错误？为什么这个工作？为什么不呢？如果OBJECT_ID（'dbo.forn'）不为空
begin $
$ b

  b $ b alter table dbo.forn drop constraint FK_forn_prim 
 DROP TABLE dbo.forn; 
 end 
 IF OBJECT_ID（'dbo.prim'）不为空
 DROP TABLE dbo.prim; 
 go 
 
 CREATE TABLE dbo.prim（c1 int PRIMARY KEY）; 
 CREATE TABLE dbo.forn（c1 int）; 
 alter table dbo.forn with nocheck add CONSTRAINT FK_forn_prim FOREIGN KEY（c1）REFERENCES dbo.prim（c1）; 
 alter table dbo.forn检查CONSTRAINT FK_forn_prim; 
 go 
 
  - 实际上违反了外键约束违规
 insert dbo.forn values（2）; 
 
  - 作品！ 
 INSERT INTO dbo.prim 
 OUTPUT inserted.c1 INTO dbo.forn 
 SELECT 1; 
  

解决方案

系统仍然不信任外键带有检查语句。这是因为现有约束的默认值是WITH NOCHECK，所以你正在运行：

  alter table dbo.forn with nocheck检查CONSTRAINT FK_forn_prim; 
  

重新启用的正确语句是：

  alter table dbo.forn with check check CONSTRAINT FK_forn_prim; 
  

嵌套插入在运行之后会再次失败。不建议离开FK不信任，因为SQL不会考虑它的许多操作。

您可以在系统视图sys.foreign_keys，is_not_trusted字段中检查不可信的FK。 p>

更多信息： http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx

The base example is lifted from No way to use TSQL Output with normal foreign key constraints?; his code fails as expected. But if the way the constraint is defined in the example is modified as below, defining the FK constraint WITH NOCHECK and then CHECKing it, the OUTPUT INTO will run unimpeded.

This seems to contradict the OUTPUT clause docs. Specifically:

output_table [the table receiving the INTO] cannot:

•Have enabled triggers defined on it.

•Participate on either side of a FOREIGN KEY constraint [emphasis added].

•Have CHECK constraints or enabled rules.

From a relational perspective the below could work, but this action is supposed to be specifically precluded. If the FK is defined as a straight "WITH CHECK" (the default) it fails as expected. If it is defined "WITH NOCHECK" and then enabled with a "CHECK CONSTRAINT" it, well, fails to fail.

It would be awesome if this was a known, supported feature. Or did I just find a bug in SQL that's persisted since at least SQL 2008 (I tested with 2008 and 2014)? Why does this work? Why shouldn't it? What am I risking by using it?

IF OBJECT_ID ('dbo.forn') IS NOT NULL
begin
    alter table dbo.forn drop constraint FK_forn_prim
    DROP TABLE dbo.forn;
end
IF OBJECT_ID ('dbo.prim') IS NOT NULL
    DROP TABLE dbo.prim;
go

CREATE TABLE dbo.prim (c1 int PRIMARY KEY);
CREATE TABLE dbo.forn (c1 int );
alter table dbo.forn with nocheck add CONSTRAINT FK_forn_prim FOREIGN KEY (c1) REFERENCES dbo.prim(c1);
alter table dbo.forn check CONSTRAINT FK_forn_prim ;
go

-- does in fact fail with foreign key constraint violation
insert dbo.forn values (2);

-- works!!
INSERT INTO dbo.prim
    OUTPUT inserted.c1 INTO dbo.forn
SELECT 1;

解决方案

The foreign key is still not trusted by the system after the with check statement. This is because the default for existing constraints is WITH NOCHECK, so you're effectively running:

    alter table dbo.forn with nocheck check CONSTRAINT FK_forn_prim;

Correct statement to re-enable is:

    alter table dbo.forn with check check CONSTRAINT FK_forn_prim;

The nested insert will fail again after running that. Not recommended to leave the FK untrusted as SQL will not consider it for many operations.

You can check for untrusted FK's in system view sys.foreign_keys, is_not_trusted field.

More here: http://sqlblog.com/blogs/hugo_kornelis/archive/2007/03/29/can-you-trust-your-constraints.aspx

这篇关于为什么这个T-SQL OUTPUT INTO FOREIGN KEY hack工作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！