Re: FK-like constraint referencing union of external tables?
From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 21 Aug 2009 13:53:22 -0700
Message-ID: <4a8f0942$1_at_news.victoria.tc.ca>
Mark D Powell (Mark.Powell_at_eds.com) wrote:
: On Aug 20, 10:59=A0pm, 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.
: >
: > : =A0 =A0 =A0CONSTRAINT FOO_FK1 FOREIGN KEY(NAME) REFERENCES UNITS(NAME) =
: ENABLE,
: > : =A0 =A0 =A0CONSTRAINT 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. =A0First is not
: > create it at all and simply handle both columns when you need to. =A0Seco=
: nd
: > would be a view that combines the columns and then use the view instead o=
: f
: > the table (perhaps with an INSTEAD of trigger to allow direct updates to
: > the view). =A0Third would be to add an extra column called
: > UNIT_NAME_OR_DEPT_NAME with constraint like (UNIT_NAME_OR_DEPT_NAME =3D
: > UNIT_NAME or UNIT_NAME_OR_DEPT_NAME =3D DEPT_NAME) =A0and then use trigge=
: rs to
: > populate the appropriate column(s) as the rows are inserted and updated.
: >
: > $0.10
Date: 21 Aug 2009 13:53:22 -0700
Message-ID: <4a8f0942$1_at_news.victoria.tc.ca>
Mark D Powell (Mark.Powell_at_eds.com) wrote:
: On Aug 20, 10:59=A0pm, 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.
: >
: > : =A0 =A0 =A0CONSTRAINT FOO_FK1 FOREIGN KEY(NAME) REFERENCES UNITS(NAME) =
: ENABLE,
: > : =A0 =A0 =A0CONSTRAINT 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. =A0First is not
: > create it at all and simply handle both columns when you need to. =A0Seco=
: nd
: > would be a view that combines the columns and then use the view instead o=
: f
: > the table (perhaps with an INSTEAD of trigger to allow direct updates to
: > the view). =A0Third would be to add an extra column called
: > UNIT_NAME_OR_DEPT_NAME with constraint like (UNIT_NAME_OR_DEPT_NAME =3D
: > UNIT_NAME or UNIT_NAME_OR_DEPT_NAME =3D DEPT_NAME) =A0and then use trigge=
: rs 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 --
Simply checking the values in a trigger as Mark suggests would be easiest. I do notice some downfalls.
If a parent value is altered (for example DELETE UNITS WHERE PK=etc) then the validated value in UNIT_NAME_OR_DEPT_NAME can become invalid.
Also, you lose the uefulnes of tables such as USER_CONSTRAINTS which show you the relationships between tables. Received on Fri Aug 21 2009 - 15:53:22 CDT