Re: How to list rows violating constraints, from the exceptions table

For example. let us take a master table dept with following fields

dept_no number(5),
dept_name varchar2(30)

Detail table EMP with following fields

emp_no number(3),
dept_no number(5)
emp_name varchar2(30)

Lets us create a foreign constraint on EMP.DEPT_NO referencing DEPT.DEPT_NO. If this fails, because of parent keys not found, then issue the following statement.

SELECT E.EMP_NO, E.DEPT_NO, E.EMP_NAME, E.ROWID FROM EMP E
WHERE NOT EXISTS ( SELECT 'X' FROM DEPT D

                   WHERE D.DEPT_NO = E.DEPT_NO)

This will return all the EMP rows where its dept number does not exists in DEPT table.

Hope the above helps

N.Prabhakar
Socgen Crosby Securities.
Singapore Received on Sat Nov 09 1996 - 00:00:00 CET