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: https://sqlserverfast.com/blog/hugo/2007/03/can-you-trust-your-constraints/
Your statement that outputs the created value into the second table works because you are going the opposite direction of your foreign key constraint. The inner statement is dependent upon the outer statement so it will be executed after the record gets created in the other table.
SQL Server will only check this foreign key when records dbo.forn are created or updated. As long as you insert into dbo.prim before dbo.forn your code will execute.
Works:
INSERT INTO dbo.prim
OUTPUT inserted.c1 INTO dbo.forn
Fails:
INSERT INTO dbo.forn
OUTPUT inserted.c1 INTO dbo.prim
Edit:
Microsoft probably choose to shortcut documenting what is probably a fairly complex scenario by saying you should not expect it to work. I would bet that based on the specifics listed for OUTPUT [INTO] with UPDATE and DELETE statements, a similar attempt with UPDATE or DELETE would fail.
{INSERT, UPDATE, DELETE} * {Types of Constraints} * {Types of Columns} * {Types of triggers} * {Whatever Else I Missed} turns into an amazingly large number of combinations to document.
As usual, empirical evidence wins, your scenario is possible.
Pretty sure the answer is that is an implicit transaction and constraint checking is at the end of the transaction
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 (1);
-- works!!
INSERT INTO dbo.prim
OUTPUT inserted.c1 INTO dbo.forn
SELECT 2;
-- does in fact fail with foreign key constraint violation
INSERT INTO dbo.prim
OUTPUT inserted.c1 + 1 INTO dbo.forn
SELECT 5;
select * from dbo.prim; -- 2 insert - it really did work
select * from dbo.forn; -- 2 insert - it really did work
