Обсуждение: VIEWs and FOREIGN keys
can someone have a foreign key constraint that references
a view??
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr
On Mon, 10 Jun 2002, Achilleus Mantzios wrote: > > can someone have a foreign key constraint that references > a view?? Not currently in PostgreSQL. Doing so for a general case gets rather involved.
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).
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
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. Terry Fielder Network Engineer Great Gulf Homes / Ashton Woods Homes terry@greatgulfhomes.com > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jan Wieck > Sent: Monday, June 10, 2002 1:20 PM > To: Achilleus Mantzios > Cc: pgsql-general@postgresql.org; pgsql-sql@postgresql.org > Subject: Re: [GENERAL] [SQL] VIEWs and FOREIGN keys > > > 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). > > > Jan > > -- > > #============================================================= > =========# > # It's easier to get forgiveness for being wrong than for > being right. # > # Let's break this rule - forgive me. > # > #================================================== > JanWieck@Yahoo.com # > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
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 #
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@greatgulfhomes.com > -----Original Message----- > From: Jan Wieck [mailto:janwieck@yahoo.com] > Sent: Monday, June 10, 2002 3:20 PM > To: terry@greatgulfhomes.com > Cc: 'Jan Wieck'; 'Achilleus Mantzios'; pgsql-general@postgresql.org; > pgsql-sql@postgresql.org > Subject: Re: [GENERAL] [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 # >
terry@greatgulfhomes.com writes:
> 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.
Got it in one.
One could perhaps imagine putting triggers on the table(s) that are
referenced by the view, but those triggers would have to do very slow
and expensive things to enforce the deletion constraint, at least in the
interesting cases where the view is a join, aggregation, GROUP BY, etc...
regards, tom lane
ok thnx to all.
I did the trick with a custom trigger.
--
Achilleus Mantzios
S/W Engineer
IT dept
Dynacom Tankers Mngmt
tel: +30-10-8981112
fax: +30-10-8981877
email: achill@matrix.gatewaynet.com
mantzios@softlab.ece.ntua.gr