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

: 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?

: Many TIA!!
: Mark

: --
: Mark Harrison
: Pixar Animation Studios

Perhaps you can do something funky with a function based index, but otherwise I don't think you can do this.

You can do the following. Create two columns UNIT_NAME, and DEPT_NAME, both allow nulls. Create a constraint something like (UNIT_NAME is null and DEPT_NAME is not null OR UNIT_NAME is not null and DEPT_NAME is null). Create two FKs, one per column.

There are then various ways to create a combined column. First is not create it at all and simply handle both columns when you need to. Second would be a view that combines the columns and then use the view instead of the table (perhaps with an INSTEAD of trigger to allow direct updates to the view). Third would be to add an extra column called UNIT_NAME_OR_DEPT_NAME with constraint like (UNIT_NAME_OR_DEPT_NAME = UNIT_NAME or UNIT_NAME_OR_DEPT_NAME = DEPT_NAME) and then use triggers to populate the appropriate column(s) as the rows are inserted and updated.

$0.10 Received on Thu Aug 20 2009 - 21:59:24 CDT