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,
INSERT tblOrder VALUES(1,'PC'),(2,'Laptop')
CREATE TABLE tblOrderHistory(
OrderId INT PRIMARY KEY REFERENCES tblOrder,
HistoryDate DATETIME DEFAULT(GETDATE())
Now if we will execute following sql query:
DELETE FROM tblOrder
DELETE FROM tblOrderHistory
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.
DROP the foreign key relationship between them. In this example:
ALTER TABLE tblOrderHistory DROP CONSTRAINT FK__tblOrderH__Order__267ABA7A
Note: Replace the constraint name.