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

Re: protected ON DELETE CASCADE

From: "Oliver Elphick" <olly(at)lfix(dot)co(dot)uk>
To: Murray Hobbs <murray(at)efone(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: protected ON DELETE CASCADE
Date: 2001-08-23 19:36:25
Message-ID: 200108231936.f7NJaQoa026107@linda.lfix.co.uk (view raw or flat)
Thread:
Lists: pgsql-novice
Murray Hobbs wrote:
  >
  >i neglected to show it properly
  >
  >have tables A, B, C, D PLUS a few others
  >
  >A <- B
  >
  >     F
  >     |
  >     v
  >A <- C <- D
  >     ^
  >     |
  >     E     
  >
  >i want to delete from C and cascade any delete to E or F but not if
  >there are records in D
  >
  >what i have done is to have ON DELETE CASCADE on C's primary
  >
  >but force deletes to C through a function that will delete from C only
  >if there is no records in D
  >
  >but i would like to believe there is a better way - a way that does not
  >require that i do all my deletes through a function
 
B REFERENCES A ON DELETE CASCADE
C REFERENCES A ON DELETE CASCADE
D REFERENCES C ON DELETE NO ACTION
E REFERENCES C ON DELETE CASCADE
F REFERENCES C ON DELETE CASCADE

So if there is a deletion in A it cascades to B and C

The deletion in C cascades to E and F, but fails if there is a
reference in D.  The failure causes a rollback of the transaction and
thus the deletions in A, B, C, E and F do not happen after all.

-- 
Oliver Elphick                                Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight                              http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47  6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "For God hath not appointed us to wrath, but to obtain 
      salvation by our Lord Jesus Christ, Who died for us, 
      that, whether we wake or sleep, we should live  
      together with him."     
                         I Thessalonians 5:9,10 



Responses

pgsql-novice by date

Next:From: Jan WieckDate: 2001-08-23 20:21:07
Subject: Re: protected ON DELETE CASCADE
Previous:From: Murray HobbsDate: 2001-08-23 19:07:52
Subject: Re: protected ON DELETE CASCADE

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