Re: [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 #