Re: Foreign keys/unique values and views

Einar Karttunen wrote:
> On Thu, 22 Mar 2001, Richard Huxton wrote:
> >
> > I have to admit I've never tried referencing a view with a foreign key. I
> > don't know if it's possible and I have to admit the idea makes me
> > uncomfortable. Can't give a good reason why, but I'd apply constraints at
> > the table level.
> if one can reference a table with a foreign key it makes possible to
> reference inheritance hierarchies. If I create a view from the parent
> with CREATE VIEW name SELECT * FROM parent; (in v.7.1) it should contain
> the entries from the children as well. Now if I could reference this view
> I could simulate referencing parent and child tables easily.
> What I have in mind is a hierarchy of persons all inheriting from a table
> called person. Different kinds of persons have different attributes but
> all have an id and name. What I want is that other tables could reference
> these persons easily.

Inheritance isn't supported in FOREIGN KEY, and cannot.
Remember that it's not only the check if the referenced key
exists on FK insert and update. FOREIGN KEY ensures that you
cannot remove those PKs or has automated actions defined for
that case. We don't have trigger inheritance. And since one
of the requirements of FOREIGN KEY is that you have a UNIQUE
constraint on your PK, this cannot be done with FOREIGN KEY.
How do you put a UNIQUE constraint on the entire inheritance
hierarchie?

You can create custom triggers that check for whatever you
want. But FOREIGN KEY has to follow the SQL specs. All
things fit together, if one doesn't fit, don't force it - use
a bigger hammer. We have sledge hammers available in all
sizes :-)

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com