Thread: Deleting Problem
Here is my problem I have a level structure which is 5 levels deep with 6 tables, for this example i will call it table1,table2,table3,table4,table5,table6 (1)table1 (2)table2 (3)table3 (4)table4 (5)table5,table6 (6)table7,table8 table5 and table6 have fk keys pointing to table1,table2,table3,table4. table7 and table 8 have fk keys pointing to table5. Here is my problem i have delete triggers on table5 and table6 which update summary information on table1,table2,table3,table4 if table5 or 6 gets delete, problem i am faced with is when eg table1 record gets deleted i get the following error Error insert or update on table5 violates foreign key constraint "table4". They are all link with a cascade delete. Machine Windows 2003, Postgresql 8.1.4 Any help would be useful. Thank you.
On Tue, 31 Oct 2006, Jamie Deppeler wrote: > Here is my problem > > I have a level structure which is 5 levels deep with 6 tables, for this > example i will call it table1,table2,table3,table4,table5,table6 > > (1)table1 > (2)table2 > (3)table3 > (4)table4 > (5)table5,table6 > (6)table7,table8 > > table5 and table6 have fk keys pointing to table1,table2,table3,table4. > table7 and table 8 have fk keys pointing to table5. > > Here is my problem i have delete triggers on table5 and table6 which > update summary information on table1,table2,table3,table4 if table5 or 6 > gets delete, problem i am faced with is when eg table1 record gets > deleted i get the following error Error insert or update on table5 > violates foreign key constraint "table4". They are all link with a > cascade delete. We're probably not going to be much help without more information about the details of the triggers and tables involved such as what operations the triggers do to do the summary update and whether they're defined as before or after triggers.
On Tue, 2006-10-31 at 10:24 +1100, Jamie Deppeler wrote:
As Stephen said, it's difficult to help without more details about the triggers and the structure of your transaction. However, if you expect that the constraints are consistent at the end of the transaction, perhaps the problem is merely the order in which constraints are checked. See
http://www.postgresql.org/docs/8.1/interactive/sql-set-constraints.html
and consider something like SET CONSTRAINTS ALL DEFERRED .
-Reece
Here is my problem i have delete triggers on table5 and table6 which
update summary information on table1,table2,table3,table4 if table5 or 6
gets delete, problem i am faced with is when eg table1 record gets
deleted i get the following error Error insert or update on table5
violates foreign key constraint "table4". They are all link with a
cascade delete.
As Stephen said, it's difficult to help without more details about the triggers and the structure of your transaction. However, if you expect that the constraints are consistent at the end of the transaction, perhaps the problem is merely the order in which constraints are checked. See
http://www.postgresql.org/docs/8.1/interactive/sql-set-constraints.html
and consider something like SET CONSTRAINTS ALL DEFERRED .
-Reece
-- Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0 |