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

From: Tim X <>
Date: Sun, 23 Aug 2009 10:19:31 +1000
Message-ID: <>

Mark D Powell <> writes:

> On Aug 21, 3:42 pm, Ruud de Koter <> wrote:
>> Hi Mark,
>> > 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.
>> 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.
>> > 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?
>> 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
> 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 --

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


tcross (at) rapttech dot com dot au
Received on Sat Aug 22 2009 - 19:19:31 CDT

Original text of this message