CODEWITHSUNDEEP
sqlsql-serverself-join
Saravana Santhosh
Saravana Santhosh

Reputation: 197

Self reference to the table

I'm new to SQL Server.

I want to reference the same table column as below,

CREATE TABLE JOIN_DEPARTMENTS
(
  DEPTID INT PRIMARY KEY,
  DEPTNAME VARCHAR(20)
);

CREATE TABLE JOIN_EMPLOYEES
(
  EMPID INT PRIMARY KEY,
  EMPNAME VARCHAR(20),
  MGRID INT,
  DEPTID INT FOREIGN KEY REFERENCES JOIN_DEPARTMENTS(DEPTID),
  CONSTRAINT FK_SELFREFE FOREIGN KEY (MGRID) REFERENCES EMPLOYEES(EMPID) ON DELETE SET NULL
);

INSERT INTO JOIN_DEPARTMENTS VALUES (100, 'BFS');
INSERT INTO JOIN_DEPARTMENTS VALUES (101, 'MELT');

INSERT INTO JOIN_EMPLOYEES VALUES(1, 'SARA', NULL, 100);  --> inserts fine
INSERT INTO JOIN_EMPLOYEES VALUES(2, 'SANTHOSH', 1, 100); -->

Throws the following error

Msg 547, Level 16, State 0, Line 530
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SELFREFE". The conflict occurred in database "SARA", table "dbo.EMPLOYEES", column 'EMPID'.

What I am doing wrong, here?

Upvotes: 0

Views: 2573

Answers (1)

Robert Sheahan
Robert Sheahan

Reputation: 2100

Your constraint references EMPLOYEES but you are inserting into JOIN_EMPLOYEES - check EMPLOYEES to make sure an EMPID=1 exists, or modify your constraint to check JOIN_EMPLOYEES instead of EMPLOYEES

EDIT: In response to comment: I don't know how to make the self referencing ON DELETE SET NULL constraint work, but I think you could accomplish the same goal by creating an AFTER DELETE trigger that set MGRID to NULL in all rows where MGRID = EMPID of the deleted row(s). I can't fully test this, but I think this will work

CREATE TRIGGER trJOIN_EMPLOYEE_AfterDel   ON  JOIN_EMPLOYEES   AFTER DELETE
AS BEGIN
    SET NOCOUNT ON;
    UPDATE JOIN_EMPLOYEES SET MGRID = NULL
    WHERE MGRID IN (SELECT EMPID FROM deleted)
END

Basically what it does is after your delete has been processed, but before the transaction is considered complete, it goes back and also sets to NULL any MGRIDs that referenced the recently dropped employee ID.

Note that Microsoft has some relevant info
http://technet.microsoft.com/en-us/library/ms186973%28v=sql.105%29.aspx
says "The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references." so I don't think you're going to get the self referential constraint to work.

Also note that you might have to remove some or all of the constraints before it will let you create that trigger, I think it's just INSTEAD OF DELETE triggers that conflict, but there is a chance AFTER DELETE triggers might conflict with some constraints - again, not enough experience here for me to be certain, try it and see.

Upvotes: 2

Related Questions