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@Yahoo.com #



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