sql server - Why does this T-SQL OUTPUT INTO with FOREIGN KEY hack work? -

The base example is raised; As expected, his code fails, but if the barrier is defined in the example, it has been modified in the form below, define the force of fiqh with nokacak and Then check In these, the output will run unexpired. / P>

It seems that is the opposite. Specifically:

output_table [INTO table] can not:

• The triggers defined on this are enabled.

• Participate on

• There are deficiencies in check or competent rules.

can work from a relational perspective, but this action should be especially predecessor. If FK is defined directly as "without check" (default), then it fails as expected, if it is defined "with noncakec" and then enabled with "checkcontent" So, fails to fail.

It would be terrible if it was a known, supported feature or did I just get a bug in SQL that was confirmed at least since SQL 2008 (I tested with 2008 and 2014) Used to be? Why does this work? Why should not this happen? What am I doing by using it?

  If objeekind id is not ('doobo fern') then the table starts to change. Drop down hurdle FK_forn_prim DROP TABLE dbo.forn; End IF OBJECT_ID ('dbo.prim') Null Drop Table is not Dbo.prim; Get the tablet dbo.prim (c1 int primary key); Create tablet dbo.forn (c1 int); Converting table dbo.forn with Nocheck CONSTRAINT FK_forn_prim foreign key (c1) reference dbo.prim (c1); Table Dabo Change the Forward Check CONSTRAINT FK_forn_prim; Go - In fact the foreign key fails with the infringement violation. Dbo.forn Price (2); - Works !! Insert Dbo.prim output. Dbo.forn SELECT 1 in C1;  

The foreign key with the check statement is still not trusted by the system because it is The current for the current constraints is with NOCHECK, so you are running effectively:

  Changing the table with dbo.forn nocheck check CONSTRAINT FK_forn_prim;  

The correct statement for re-enabling is:

  Changing the table with the dbo.forn check check CONSTRAINT FK_forn_prim;  

After nested insert will fail again. SQL will not be considered for many tasks as it is not recommended to leave the FK.

You can see in the incredible FK system view, sys.foreign_keys, is_not_trusted field.

More here: