Re: FK-like constraint referencing union of external tables?
Date: Sat, 22 Aug 2009 07:44:20 -0700 (PDT)
Message-ID: <a9633373-c981-4148-8ecd-bdff66df9673_at_j21g2000yqe.googlegroups.com>
On Aug 21, 3:42 pm, Ruud de Koter <nob..._at_internet.org> 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.
>
> > 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?
>
> 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 -- Received on Sat Aug 22 2009 - 09:44:20 CDT