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

Suppose we have created two tables tblOrder and tblOrderHistory and inserted some data into it by executing following sql queries:

CREATE TABLE tblOrder(

     OrderId INT PRIMARY KEY,

     OrderName VARCHAR(100)

)

INSERT tblOrder VALUES(1,'PC'),(2,'Laptop')

CREATE TABLE tblOrderHistory(

     OrderId INT PRIMARY KEY REFERENCES tblOrder,

     OrderName VARCHAR(100),

     HistoryDate DATETIME DEFAULT(GETDATE())

)

Now if we will execute following sql query:

DELETE FROM tblOrder

OUTPUT deleted.OrderId,DELETED.OrderName

INTO tblOrderHistory(OrderId,OrderName)

Or

DELETE FROM tblOrderHistory

OUTPUT deleted.OrderId,DELETED.OrderName

INTO tblOrder(OrderId,OrderName)

We will get error message like :

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

Cause: There should not be foreign key relationship between source and target table if we are using OUTPUT INTO clause.

Solution:

DROP the foreign key relationship between them.  In this example:

ALTER TABLE tblOrderHistory DROP CONSTRAINT FK__tblOrderH__Order__267ABA7A

Note: Replace the constraint name.