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.
No comments:
Post a Comment