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

From: Mark D Powell <>
Date: Fri, 21 Aug 2009 07:08:56 -0700 (PDT)
Message-ID: <>

On Aug 20, 10:59 pm, (Malcolm Dew-Jones) wrote:
> wrote:
> : 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.
> : 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 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

You cannot have one column with an either or FK constraint since the ANSI standard defines a FK as being to he PK (or UK) of a single target table; however, using table level triggers you could verify the value exists in either Table_A or Table_B prior to excepting the data. That would be easy to do.

HTH -- Mark D Powell -- Received on Fri Aug 21 2009 - 09:08:56 CDT

Original text of this message