Re: FK-like constraint referencing union of external tables?
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Fri, 21 Aug 2009 07:08:56 -0700 (PDT)
Message-ID: <dc0472b9-68fc-476c-af92-978cfa0c2089_at_t13g2000yqt.googlegroups.com>
On Aug 20, 10:59 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> m..._at_pixar.com 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.
>
> : 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
Date: Fri, 21 Aug 2009 07:08:56 -0700 (PDT)
Message-ID: <dc0472b9-68fc-476c-af92-978cfa0c2089_at_t13g2000yqt.googlegroups.com>
On Aug 20, 10:59 pm, yf..._at_vtn1.victoria.tc.ca (Malcolm Dew-Jones) wrote:
> m..._at_pixar.com 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.
>
> : 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
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