tsql - 没有办法在正常的外键约束下使用TSQL Output？

Question

以下代码段因错误而失败:

The target table 'dbo.forn' of the OUTPUT INTO clause cannot be on either side of a (primary key, foreign key) relationship. Found reference constraint 'FK_forn_prim'."

我只能通过禁用外键约束来使用输出吗？如何才能做到这一点？

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

根据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 correspond.

基于此，有很多限制，其中有一个您上面遇到的限制，但是如果您只想将插入的值记录在第二个表中，我看不到为什么您需要定义的外键关系以上。

如果确实需要外键关系，则可以通过使用这样的触发器来完成相同的操作。

    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;