From: Jan Wieck <janwieck@yahoo.com>
Message-Id: <200206101919.g5AJJaF07199@saturn.janwieck.net>
Subject: Re: [SQL] VIEWs and FOREIGN keys
In-Reply-To: <002801c210af$9088c980$2766f30a@development.greatgulfhomes.com>
	from "terry@greatgulfhomes.com" at "Jun 10, 2002 02:49:33 pm"
To: terry@greatgulfhomes.com
Date: Mon, 10 Jun 2002 15:19:36 -0400 (EDT)
Cc: "'Jan Wieck'" <janwieck@yahoo.com>,
	"'Achilleus Mantzios'" <achill@matrix.gatewaynet.com>,
	pgsql-general@postgresql.org, pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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