Re: Foreign keys/unique values and views

On Thu, 22 Mar 2001, 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.

Currently the reltype of the referenced thing has to be a table, plus I
don't think you can currently put a unique constraint on views either
which would be a prerequisite.

The reason for this is precisely the kind of thing you're trying to do.
The system doesn't currently know enough to be able to do this. With the
triggers, we'd have to add triggers to the parent and children table for
update and delete, but we'd have to know what they were, and if you
inherit a new table, that one would need it to. Plus, what if the
view has where clauses, etc, you have to work out when you would need
to limit things, etc. If we ever do enough stuff to allow generic
updatable views (beyond really simple ones), we'd probably be able to do
this, but until then...