Re: [SQL] VIEWs and FOREIGN keys

Right, I had forgotten that the foreign key is supposed, to be unique.

Not withstanding that, a general solution would have to handle more then
just updates via the query, it would also have to handle updates done
directly to (one or more) of the views source table.

Therefore, ultimately you will need a trigger in ALL the source tables for
DELETE/UPDATE events, and they would need to know that logic of the VIEW's
WHERE clause and how it restricts result rows, therefore it could then
determine if the change to the underlying table would break the FK
constraint on the view.

Having said that, I am sure I could write pseudocode to do this.

However, given the fact that:
a) the view can be very complex
b) the update/delete query to the underlying can be extremely complex (eg
nested selects, updates, IN constraints, etc)

The coded solution would get very ugly, very fast.

This clears things up for me, I wish I had a "cool idea" to implement this
functionality, but I do not. Weighed against the complexity of implementing
it, the benefit acquired is not worth the brain damage to get there.

If the user *really* needed an FK to a view, then how about setting up a
table that contains the info of the view, updated by triggers hanging off
the source tables for the view that copy the result of the view into the the
views mirror table. This is not an efficient solution, and not even viable
if the view produces a large dataset, but just a thought for the users that
really *really* want to "foreign key into a view" ...

Terry Fielder
Network Engineer
Great Gulf Homes / Ashton Woods Homes
terry(at)greatgulfhomes(dot)com

> -----Original Message-----
> From: Jan Wieck [mailto:janwieck(at)yahoo(dot)com]
> Sent: Monday, June 10, 2002 3:20 PM
> To: terry(at)greatgulfhomes(dot)com
> Cc: 'Jan Wieck'; 'Achilleus Mantzios'; pgsql-general(at)postgresql(dot)org;
> pgsql-sql(at)postgresql(dot)org
> Subject: Re: [GENERAL] [SQL] VIEWs and FOREIGN keys
>
>
> terry(at)greatgulfhomes(dot)com wrote:
> > I would just like to elaborate, and clarify if my
> understanding is correct:
> >
> > The implication of below is that you need a trigger in the
> foreign key
> > target table on the DELETE event, so the foreign key table
> only lets you
> > delete a row if there are no other tables refering to the
> key you want to
> > delete.
> >
> > Views cannot have triggers, hence cannot have a DELETE
> trigger, therefore
> > that is why the view cannot be a foreign key target table.
>
> Right, the primary key table (what you refer to as the
> foreign key target) needs to have a trigger on DELETE and
> UPDATE (the key value could change and break referential
> integrity by doing so). For simple views this might be
> doable with a trigger on the base tables, but imagine this:
>
> CREATE VIEW pk_view AS
> SELECT t1.keypart1 || t2.keypart2 AS primkey
> FROM t1, t2 WHERE t1.isactive;
>
> CREATE TABLE fk_table (
> ref varchar,
> FOREIGN KEY (ref) REFERENCES pk_view (primkey)
> );
>
> Okay, let's ignore the fact that the ANSI SQL spec requires
> every referenced key to have a UNIQUE constraint, and that we
> cannot guarantee that in the first place.
>
> We toggle t1.isactive on a row to false, thereby removing a
> few thousand result rows from the view's result set. Any cool
> idea how to check if that doesn't produce some orphaned rows
> in "fk_table"? By "cool idea" I mean not a couple hand
> crafted PL/pgSQL triggers, but some general solution that
> works with any view.
>
>
> Jan
>
> > [...]
> > > -----Original Message-----
> > > From: pgsql-general-owner(at)postgresql(dot)org
> > >
> > > Achilleus Mantzios wrote:
> > > >
> > > > can someone have a foreign key constraint that references
> > > > a view??
> > >
> > > No, and this is not planned either. Remember that it is not
> > > only required for referential integrity to check if a key
> > > exists on INSERT or UPDATE to the referencing table. The
> > > system must guarantee that you cannot remove existing keys
> > > while they are referenced (or more precise perform the
> > > requested referential action).
>
> --
>
> #=============================================================
> =========#
> # 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 #
>