How to Delete all data from a table which contain self referencing foreign key

Question

I have a table which has employee relationship defined within itself. i.e.

EmpID   Name  SeniorId
-----------------------
1         A      NULL
2         B       1
3         C       1
4         D       3

and so on...

Where Senior ID is a foreign key whose primary key table is same with refrence column EmpId I want to clear all rows from this table without removing any constraint. How can i do this?

Deletion need to be performed like this 4, 3 , 2 , 1

How can I do this

EDIT:

Jhonny's Answer is working for me but which of the answers are more efficient.

Answer 1

Inside a loop, run a command that deletes all rows with an unreferenced EmpID until there are zero rows left. There are a variety of ways to write that inner DELETE command:

 DELETE FROM employee WHERE EmpID NOT IN (SELECT SeniorID FROM employee)

 DELETE FROM employee e1 WHERE NOT EXISTS
    (SELECT * FROM employee e2 WHERE e2.SeniorID = e.EmpID

and probably a third one using a JOIN, but I'm not familiar with the SQL Server syntax for that.

Answer 2

One solution is to normalize this by splitting out "senior" relationship into a separate table. For the sake of generality, make that second table "empID1|empID2|relationship_type".

Barring that, you need to do this in a loop. One way is to do it:

declare @count int
select @count=count(1) from table
while (@count > 0)
BEGIN
    delete employee WHERE NOT EXISTS
       (select 1 from employee 'e_senior' 
        where employee.EmpID=e_senior.SeniorID)
    select @count=count(1) from table
END

Answer 3

I don't know if I am missing something, but maybe you can try this.

UPDATE employee SET SeniorID = NULL
DELETE FROM employee

Answer 4

DELETE FROM employee;

Answer 5

If the table is very large (cardinality of millions), and there is no need to log the DELETE transactions, dropping the constraint and TRUNCATEing and recreating constraints is by far the most efficient way. Also, if there are foreign keys in other tables (and in this particular table design it would seem to be so), those rows will all have to be deleted first in all cases, as well.

Normalization says nothing about recursive/hierarchical/tree relationships, so I believe that is a red herring in your reply to DVK's suggestion to split this into its own table - it certainly is viable to make a vertical partition of this table already and also to consider whether you can take advantage of that to get any of the other benefits I list below. As DVK alludes to, in this particular design, I have often seen a separate link table to record self-relationships and other kinds of relationships. This has numerous benefits:

• have many to many up AND down instead of many-to-one (uncommon, but potentially useful)
• track different types of direct relationships - manager, mentor, assistant, payroll approver, expense approver, technical report-to - with rows in the relationship and relationship type tables instead of new columns in the employee table
• track changing hierarchies in a temporally consistent way (including terminated employee hierarchy history) by including active indicators and effective dates on the relationship rows - this is only fully possible when normalizing the relationship into its own table
• no NULLs in the SeniorID (actually on either ID) - this is a distinct advantage in avoiding bad logic, but NULLs will usually appear in views when you have to left join to the relationship table anyway
• a better dedicated indexing strategy - as opposed to adding SeniorID to selected indexes you already have on Employee (especially as the number of relationship types grows)

And of course, the more information you relate to this relationship, the more strongly is indicated that the relationship itself merits a table (i.e. it is a "relation" in the true sense of the word as used in relational databases - related data is stored in a relation or table - related to a primary key), and thus a normal form for relationships might strongly indicate that the relationship table be created instead of a simple foreign key relationship in the employee table.

Benefits also include its straightforward delete scenario:

DELETE FROM EmployeeRelationships;
DELETE FROM Employee;

You'll note a striking equivalence to the accepted answer here on SO, since, in your case, employees with no senior relationship have a NULL - so in that answer the poster set all to NULL first to eliminate relationships and then remove the employees.

There is a possibly appropriate usage of TRUNCATE depending upon constraints (EmpployeeRelationships is typically able to be TRUNCATEd since its primary key is usually a composite and not a foreign key in any other table).