Deleting Referential Integrity Violations - SQL Server

Problem

You wish to delete records from a table when those records refer to nonexistent records in some other table. Example: some employees are assigned to departments that do not exist. You wish to delete those employees.

Solution

Use the NOT EXISTS predicate with a subquery to test the validity of department numbers:

 delete from emp
  where not exists (
    select * from dept
     where dept.deptno = emp.deptno
 )

Alternatively, you can write the query using a NOT IN predicate:

 delete from emp
 where deptno not in (select deptno from dept)

Discussion

Deleting is really all about selecting: the real work lies in writing WHERE clause conditions to correctly describe those records that you wish to delete.

The NOT EXISTS solution uses a correlated subquery to test for the existence of a record in DEPT having a DEPTNO matching that in a given EMP record. If such a record exists, then the EMP record is retained. Otherwise, it is deleted. Each EMP record is checked in this manner.

The IN solution uses a subquery to retrieve a list of valid department numbers. DEPTNOs from each EMP record are then checked against that list. When an EMP record is found with a DEPTNO not in the list, the EMP record is deleted.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table