Skip site navigation (1) Skip section navigation (2)

Re: BUG #3296: CASCADING FKs dont DEFER

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "S(dot) Robert James" <srobertjames(at)vesaria(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3296: CASCADING FKs dont DEFER
Date: 2007-05-22 13:57:49
Message-ID: 12066.1179842269@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"S. Robert James" <srobertjames(at)vesaria(dot)com> writes:
> If a FK is set to ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED, it does
> not honor the defer.

> That is, if you DELETE a record and then INSERT it back, you still get a FK
> deletion of the referencing record.  This seems to ignore DEFERRED.

Actually, the cascaded delete happens immediately regardless of "defer"
mode; you can only defer constraint checks, not cascade actions.  As far
as I can tell, this is the behavior defined by the SQL spec.  Notice in
particular this statement (from SQL92 11.8 <referential constraint
definition>)

         9) All rows that are marked for deletion are effectively deleted
            at the end of the SQL-statement, prior to the checking of any
            integrity constraints.

which makes it clear that the committee thinks that cascade deletes are
separate from constraint checks.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Stephan SzaboDate: 2007-05-22 14:25:52
Subject: Re: ON DELETE CASCADE with multiple paths
Previous:From: adam terreyDate: 2007-05-22 08:45:34
Subject: Inconsistant SQL results - Suspected error with query planing or query optimisation.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group