Re: [SQL] VIEWs and FOREIGN keys

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 #