Oracle: FK referring PK in multiple parent tables

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 re开发者_StackOverflowferential integrity constraint is not possible, is there a better way to maintain this data integrity ?

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.

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.

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.

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;
    /