Oracle: FK referring PK in multiple parent tables

Question

In Oracle 10g, is it possible to define referential integrity constraints for a Foreign Key to refer to Primary Key in multiple parent tables.

Eg:

DEPT_AMER

DEPT_ID
10
11
12

DEPT_APAC

DEPT_ID
13
14
15

DEPT_EMEA

DEPT_ID
16
17
18

EMP

EMP_ID DEPT_ID
500 11
501 15

I want EMP.DEPT_ID to be able to refer to one of the department ids in DEPT_AMER, DEPT_APAC & DEPT_AMER. Is there a way to define a referential integrity to meet this need. The DEPT_ID in all the 3 tables is generated from a common sequence and are guaranteed to be unique.

If referential integrity constraint is not possible, is there a better way to maintain this data integrity ?

score 6 · Answer 1 · answered Nov 28 '12 at 15:33

if fields in all tables are same then I would suggest to refractor this model to single table and to create views for legacy applications. Sometimes this design is used for partitions but Oracle maintains partitions automatically and application level partitioning is redundant.

score 6 · Answer 2 · edited Mar 29 '11 at 17:29

6

You have one entity in three different tables. The best way is to join DEPT_AMER + DEPT_EMEA + DEPT_APAC into the one table called DEPT with new field DEPT_TYPE (AMER or EMEA or APAC). It's better for feature support and performance.

What will you do if new department opens in the Antarctic? Add another table? No! You just add another dept_type.

edited Mar 29 '11 at 17:29

APC

144,005
19
170
281

answered Mar 29 '11 at 15:11

drnk

764
2
9
18

1

And what's preventing a department ID being used in more than one of the parent tables? – Alex Poole Mar 29 '11 at 15:14
6

Actually, a department in Antarctica is exactly where I'd send somebody who proposed a data model with multiple parent tables. – APC Mar 29 '11 at 17:28

DCookie · Answer 3 · 2011-03-29T15:53:49.363

You can define the constraints, but it's not going to do what you want. You'll never be able to add anything to the emp table, because the key DEPT_ID will have to reside in each of the DEPT_ tables.

Assuming you must keep the existing structure, the simplest thing is to define a materialized view that merges each of these tables into one view. IMO, this is a flawed implementation. I would have created one table for the DEPT_ info, with a column that split the various types.

schurik · Answer 4 · 2011-03-29T16:06:34.297

1

You can't define such a FK-constraint. But you can verify the data integrity with a trigger. For example:

 CREATE OR REPLACE TRIGGER emp_check_dept_id_trg
    BEFORE INSERT OR UPDATE 
    on emp
    FOR EACH ROW
    DECLARE
      l_res NUMBER;
    BEGIN
      SELECT count(*) 
      INTO l_res
      FROM (
              SELECT dept_id FROM DEPT_AMER WHERE dept_id = :NEW.DEPT_ID
            UNION ALL
                SELECT dept_id FROM DEPT_APAC WHERE dept_id = :NEW.DEPT_ID
            UNION ALL
                SELECT dept_id FROM DEPT_EMEA WHERE dept_id = :NEW.DEPT_ID
          )
      ;
      IF l_res = 0 THEN
        raise_application_error(-20000, 'referential integrity violated');
      END IF;
    END;
    /

edited Mar 29 '11 at 16:06

answered Mar 29 '11 at 15:29

schurik

7,798
2
23
29

3

You can try, but trigger-based RI is almost never 100% reliable. You would need to lock the DEPT_* rows to prevent them being deleted by another session, and so on. – Tony Andrews Mar 29 '11 at 15:39
That's right. But when a schema change is not possible, for example due to external applications. Which alternatives then remain. – schurik Mar 29 '11 at 16:13

Oracle: FK referring PK in multiple parent tables

DEPT_AMER

DEPT_APAC

DEPT_EMEA

EMP

4 Answers4