Thread: Deleting Problem

Deleting Problem

From
Jamie Deppeler
Date:
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.

Re: Deleting Problem

From
Stephan Szabo
Date:
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.


Re: Deleting Problem

From
Reece Hart
Date:
On Tue, 2006-10-31 at 10:24 +1100, Jamie Deppeler wrote:
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