From: Jan Wieck <janwieck@yahoo.com>
To: terry@greatgulfhomes.com
Cc: 'Jan Wieck' <janwieck@yahoo.com>
Cc: 'Achilleus Mantzios' <achill@matrix.gatewaynet.com>
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] VIEWs and FOREIGN keys
Date: Mon, 10 Jun 2002 15:19:36 -0400 (EDT)
Message-ID: <200206101919.g5AJJaF07199@saturn.janwieck.net> (raw)
In-Reply-To: <002801c210af$9088c980$2766f30a@development.greatgulfhomes.com>
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 #
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: janwieck@yahoo.com, terry@greatgulfhomes.com, achill@matrix.gatewaynet.com, pgsql-general@postgresql.org
Subject: Re: [SQL] VIEWs and FOREIGN keys
In-Reply-To: <200206101919.g5AJJaF07199@saturn.janwieck.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox