how to check for R/I violation

rdba · #1 (**permalink**) 09-10-09, 23:26

I want to add an R/I to two tables - one table will be parent and one child

parent table - department
child table - employee

How can I check if there are any rows that would violate this R/I? Rows that exist in the child table (employee) with no corresponding row in the parent table (department)

tonkuma · #2 (**permalink**) 09-11-09, 01:54

Code:

SELECT *
  FROM employee e
 WHERE NOT EXIXTS
       (SELECT *
          FROM department d
         WHERE d.deptno = e.workdept);

You can also use NOT IN predicate.

ARWinner · #3 (**permalink**) 09-11-09, 09:39

Almost correct.

Code:

SELECT *
  FROM employee e
 WHERE NOT EXISTS
       (SELECT *
          FROM department d
         WHERE d.deptno = e.workdept)
         and e.workdept is not null;

NULL values do not violate RI.

Andy

rdba · #4 (**permalink**) 09-11-09, 12:42

Can I use EXCEPT in place of NO EXISTS? Which one performs better?

ARWinner · #5 (**permalink**) 09-11-09, 13:13

NOT EXISTS will perform better.

Andy

Lenny77 · #6 (**permalink**) 09-11-09, 14:27

You can use also:

Quote:

SELECT *
FROM employee e
Left Join
department d
On d.deptno = e.workdept

Where d.deptno is Null

with the same result.

For better understand how it works you can play with sample:

Quote:

select emp.*, dpt.dept
from
(
select 2 empid, 1 dept from sysibm.sysdummy1
union all
select 3 empid, 5 dept from sysibm.sysdummy1
union all
select 4 empid, 7 dept from sysibm.sysdummy1 ) emp
Left Join
(
select 1 dept from sysibm.sysdummy1
union all
select 7 dept from sysibm.sysdummy1 ) dpt

on emp.dept = dpt.dept

where dpt.dept is null

First time execute without "where dpt.dept is null", then execute whole query.

Lenny

Internet Services

Web Development

Digital Marketing

Consumer Tech