Can't use TSQL Output with normal foreign key constraints?

Question

The following snippet of error with error:

The target table 'dbo.forn' from the OUTPUT INTO clause cannot be on both sides (primary key, foreign key). Found restriction link 'FK_forn_prim' ".

Can I use only output by disabling foreign key constraints? How can I do that?

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 CONSTRAINT FK_forn_prim FOREIGN KEY (c1) REFERENCES dbo.prim(c1)); go INSERT INTO dbo.prim OUTPUT inserted.c1 INTO dbo.forn SELECT 1; 

Answer 1

According to technet

There is no guarantee that the order in which the changes are applied to the table and the order in which the rows are inserted into the output table or table variable will be consistent.

Based on this, there are a number of limitations, among which you encountered above, but if all you want to do is write the inserted value to the second table, I don’t understand why you need the foreign key of the relationship that you defined above.

If you need a foreign key relationship, you can accomplish the same thing using a trigger like this.

  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 CONSTRAINT FK_forn_prim FOREIGN KEY (c1) REFERENCES dbo.prim(c1)); go CREATE TRIGGER InsertRecord ON dbo.Prim AFTER Insert AS BEGIN SET NOCOUNT ON; Insert into dbo.forn Select * from inserted; END GO INSERT INTO dbo.prim SELECT 1; 

Answer 2

Usually you print a table table or temp, and then use it to insert into the final table.

Answer 3

You are right, output statements will not work in tables with foreign key constraints.

Another option is to temporarily disable restrictions when inserting data. Although this should not be the norm, it works well for one-time data loads.

 ALTER TABLE dbo.forn NOCHECK CONSTRAINT FK_forn_prim GO INSERT INTO dbo.prim OUTPUT inserted.c1 INTO dbo.forn SELECT 1; ALTER TABLE dbo.forn CHECK CONSTRAINT FK_forn_prim GO 

Answer 4

The documentation states that the output table may not participate in foreign key constraints (or check constraints, and also not allow triggers defined on it), but these cases are not considered here). Msg 332 error is a manifestation of this.

( THIS IS NOT RECOMMENDED. See update below)

However, I found that this restriction is undermined if the foreign key constraint is disabled and reenabled with NOCHECK/CHECK even after re-enabling the constraint. In other words: it is enough to have one cycle with the switch off, so that the FK restriction is "invisible" to prohibit output to a foreign key. Pay attention to the modification below:

 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); -- note change here: 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 ; -- end change go INSERT INTO dbo.prim OUTPUT inserted.c1 INTO dbo.forn select 1; 

The following errors (violation of restrictions), so you know that DRI is still working:

 INSERT INTO dbo.prim OUTPUT inserted.c1 + 1 INTO dbo.forn select 2; 

Given the ban on the documents, I give it seems dubious. I have a question awaiting an answer to this question, although it was not well received.

UPDATE

The previously mentioned question has been answered , and new information will invalidate this answer. Leaving this as a warning to others, in case they stumble upon this seemingly random hole.

Result: alter TABLE dbo.forn check CONSTRAINT FK_forn_prim ; re-fixes the constraint, but leaves it in an “unreliable" state, so it cannot be fully used by the SQL engine to optimize the index, etc. This is not recommended. The right way to reuse is

 alter table dbo.forn with check check CONSTRAINT FK_forn_prim;