Apr 4, 2013

The target table '' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint ''.


Suppose we have created Employee table in current database. Now we are creating other table tblData with foreign key relationship:

CREATE TABLE tbldata(
    EmpID BIGINT PRIMARY KEY REFERENCES Employee ,
    Data VARCHAR(100),
    DeletedDate DATE DEFAULT(GETDATE())
)

If we will execute following sql query:

INSERT INTO tbldata(EmpId,Data)
SELECT  Name
FROM(
    DELETE TOP(1)
    FROM Employee
    OUTPUT DELETED.ID, DELETED.Name
) Temp

We will get error message :

The target table '' of the INSERT statement cannot be on either side of a (primary key, foreign key) relationship when the FROM clause contains a nested INSERT, UPDATE, DELETE, or MERGE statement. Found reference constraint ''.

Cause: We cannot insert records into a table which has primary key – foreign key relationship between base table and target table from a nested FROM clause.  

Solution:

So we must have to drop the foreign key relationship.

No comments:

Post a Comment