无法将 TSQL 输出与普通外键约束一起使用? [英] No way to use TSQL Output with normal foreign key constraints?

问题描述

以下代码段失败并出现错误:

The following snippet fails with error:

OUTPUT INTO 子句的目标表dbo.forn"不能位于(主键、外键)关系的任一侧.找到引用约束FK_forn_prim"."

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'."

我只能通过禁用外键约束来使用输出?这怎么办?

I can only use output by disabling foreign key constraints? How can this be done?

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;

推荐答案

根据 技术网

无法保证将更改应用于表的顺序与将行插入到输出表或表变量中的顺序一致.

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.

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

Based on this there are a number of restrictions, among which is the one you ran into above, but if all you want is to record the inserted value in a second table I don't see why you would need the foreign key relationship you define above.

如果你确实需要外键关系，你可以使用这样的触发器来完成同样的事情.

If you do need the foreign key relationship you could accomplish the same thing by 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;

这篇关于无法将 TSQL 输出与普通外键约束一起使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！