Re: [SQL] VIEWs and FOREIGN keys - Mailing list pgsql-general
From | terry@greatgulfhomes.com |
---|---|
Subject | Re: [SQL] VIEWs and FOREIGN keys |
Date | |
Msg-id | 002c01c210b7$1b088d00$2766f30a@development.greatgulfhomes.com Whole thread Raw |
In response to | Re: [SQL] VIEWs and FOREIGN keys (Jan Wieck <janwieck@yahoo.com>) |
List | pgsql-general |
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 # >
pgsql-general by date: