Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    42

    Unanswered: how to check for R/I violation

    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)

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Code:
    SELECT *
      FROM employee e
     WHERE NOT EXIXTS
           (SELECT *
              FROM department d
             WHERE d.deptno = e.workdept);
    You can also use NOT IN predicate.

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  4. #4
    Join Date
    Aug 2009
    Posts
    42
    Can I use EXCEPT in place of NO EXISTS? Which one performs better?

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    NOT EXISTS will perform better.

    Andy

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    You can use also:

    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:

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •