Oracle: FK on union of two tables?

Question

I have a situation which (simplified) looks like this:

• Table UNITS has a PK on UNITS.NAME. (unit name, varchar(12))

• Table DEPTS has a PK on DEPTS.NAME. (dept name, varchar(12))

• I want to create a table with column UNIT_NAME_OR_DEPT_NAME, with the requirement that data in this column must be either a valid unit name or a valid department name.

I tried adding two FK constraints, but found this gave me the intersection rather than the union of the two tables.

 CONSTRAINT FOO_FK1 FOREIGN KEY(NAME) REFERENCES UNITS(NAME) ENABLE,
 CONSTRAINT FOO_FK2 FOREIGN KEY(NAME) REFERENCES DEPTS(NAME) ENABLE,

How can I create a FK or other constraint that would me to reference the union of two tables?

Answer 1

Why do you want one column to point at two different tables? Why not two different columns in a Many:Many relationship? You will probably have to use a trigger to check that at least one is not null before allowing an insert.

Answer 2

I think I would do this:

Have a sequence called "MasterKey" (or whatever).

Have a third table called "SequencesUsed" or something which would have only a single column - the used values from MasterKey. When a new record is created in your unit and department tables, trigger a new record in the sequencesUsed table and put the value generated iin the appropriate table as a foriegn key.

Your new table would have a field called "dept_or_unit_id" that have theSequencesUsed table as it's foreign key.

So something like this:

MasterKey: column: ID values: 1, 2, 3, 4, Unit: column: name, masterkey values: foo, 1; bar, 3; Dept: column: name, masterkey. values: one, 2; star, 4; NewTable: column: masterkey values: 1, 2, 3, 4

there's probably a way to improve this a bit, depending on how you are using the data, but it seems like joins would be a lot easier later than if you had one field point at two places.

Answer 3

Hi Mark,

I would go with two hidden columns units_fk and dept_fk with proper FK constraints and an additionnal CHECK CONSTRAINT, for example if the pseudo-column is mandatory:

CONSTRAINT chk_units_dept_fk CHECK 
    (units_fk IS NULL AND dept_fk IS NOT NULL)
 OR (units_fk IS NOT NULL AND dept_fk IS NULL)

You could then add a shadow column in a view

CREATE VIEW my_view AS 
SELECT col1,...coln, nvl(units_fk, dept_fk) as UNIT_NAME_OR_DEPT_NAME
  FROM my_table

You could deal with updates of the pseudo-column either with procedures (my choice) or with INSTEAD OF TRIGGER.