Re: FK-like constraint referencing union of external tables?

> 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.

Sounds like you want to use some object-oriented concepts in your database programming. Apparently there is a supertype of both units and departments involved. One way to make this work is to sort of acknowledge the existence of this supertype, and give it its own table, lets naively call it THINGS. THINGS would then contain columns for the shared attributes of units and departments, while DEPTS and UNITS would have columns for the attributes that are unique to either concept.
>
> 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?

If you were to introduce a table THINGS, you could easily reference that table's primary key.

Most likely you will reply with the remark that you are not free to change the data model ;-). In fact, that might be a good thing in this case, as there are many pittfalls in the combination of object-oriented thought and relational databases.

Regards,

Ruud de Koter Received on Fri Aug 21 2009 - 14:42:58 CDT