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

mh (AT) pixar (DOT) 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

On Aug 20, 10:59*pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
wrote:
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 --

Hi Mark,

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

Mark D Powell (Mark.Powell (AT) eds (DOT) com) wrote:
: On Aug 20, 10:59=A0pm, yf... (AT) vtn1 (DOT) victoria.tc.ca (Malcolm Dew-Jones)
: wrote:
: > m... (AT) pixar (DOT) 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.

On Aug 21, 3:42*pm, Ruud de Koter <nob... (AT) internet (DOT) org> wrote:
I will just add that in my opinion a relational database should be
used to hold relational data in a fully normalized design and that
objects belong in the application layer and should not be stored in
the database. Storing objects in the database just results in data
extraction and integrity issues in the long run.

When you come across a situation where you end up trying to define
constraints that violate the constraint definition rules you almost
surely have non-normalized data. Developers have a bad habit of
working from the application side into the database and trying to
impose the application structure on the database data instead of
working from the entity attribute level, storing this in the database,
and then designing the code logic to construct what the application
needs from the data.

IMHO -- Mark D Powell --

Mark D Powell <Mark.Powell (AT) eds (DOT) com> writes:

I think you hit the nail on the head here. When I first saw this
question, two things jumped out at me -

1. the problem with defining the FK is primarily due to an attempt to
work with a de-normalised table structure and

2.What will be the PK for the new table?

I suspect the OP may be in one of those unfortunate, but all too common
situations where the application they need to modify/extend has a
denormalised form and now attempting to extend the model while
maintaining data integrity is going to become very difficult.

Would it be possible to achieve the desired outcome with a view rather
than through generating another table? this at least might prevent
another table of denormalised data that is likely to cause problems with
maintenance down the track (I'd be a little concerned about how the data
in this new table will be maintained as the data in the parent tables it
is extracted from get updated etc).

Tim


--
tcross (at) rapttech dot com dot au

On Aug 22, 5:19*pm, Tim X <t... (AT) nospam (DOT) dev.null> wrote:
See the paper referenced at http://richardfoote.wordpress.com/20...n-keys-helden/
to possibly be driven insane, if you can make it through.

jg
--
@home.com is bogus.
http://www.theage.com.au/technology/...0818-eohc.html