Thread: Cascading Deletes
Hi, I have a tables having this structure: ID (int) | _ID (int) | Name (String) _ID is the parent of ID. I'm looking for the best method to perform a cascade delete. For example, I delete _ID, it in turn deletes ID, and should ID also be an entry within _ID, it would continue through the chain. For example: 0 0 Base 1 0 Sib1 2 0 Sib2 3 0 Sib3 4 1 Sib1_1 5 1 Sib1_2 Deleting Base would remove all the entries. Deleting Sib1 would delete Sib1_1 and Sib1_2. Can anyone help out here? Regards, Craig May Enth Dimension http://www.enthdimension.com.au
Hello, I have heard of this infamous 8k limit. I have a couple of questions. 1. Does this mean that if I have a large object that I am inserting into a table, like an image it has to be 8k or less? 2. When will this be fixed? 3. Does anyone know the status of the replication capabilities in PGSQL? J On Wed, 6 Sep 2000, Craig May wrote: >Hi, > >I have a tables having this structure: > >ID (int) | _ID (int) | Name (String) > > >_ID is the parent of ID. > >I'm looking for the best method to perform a cascade delete. For example, I >delete _ID, it in turn deletes ID, and should ID also be an entry within _ID, it >would continue through the chain. > >For example: > >0 0 Base >1 0 Sib1 >2 0 Sib2 >3 0 Sib3 >4 1 Sib1_1 >5 1 Sib1_2 > > >Deleting Base would remove all the entries. Deleting Sib1 would delete Sib1_1 >and Sib1_2. >Can anyone help out here? > >Regards, >Craig May > >Enth Dimension >http://www.enthdimension.com.au > -- -- <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY> <PROJECT>OpenDocs, LLC. - http://www.opendocs.org </PROJECT> <PROJECT>LinuxPorts - http://www.linuxports.com </PROJECT> <WEBMASTER>LDP - http://www.linuxdoc.org </WEBMASTER> -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
On Tue, 5 Sep 2000, Poet/Joshua Drake wrote:
> Hello,
>
> I have heard of this infamous 8k limit. I have a couple of questions.
> 1. Does this mean that if I have a large object that I am inserting into a
> table, like an image it has to be 8k or less?
In current version is possible range 8--32Kb for block size, default is 8Kb.
You can change it in sourses in the file include/config.h, other solution is
use the large object interface (LO).
> 2. When will this be fixed?
It's already fixed in the current devel tree (see CVS) and it will
available in 7.1 (1 Oct?).
> 3. Does anyone know the status of the replication capabilities in PGSQL?
Good question, bad answer ... (IMHO) not exist some standard replication
for PG.
Karel
I think a references constraint on ID referencing _ID with ON DELETE CASCADE should do what you want. Stephan Szabo sszabo@bigpanda.com On Wed, 6 Sep 2000, Craig May wrote: > Hi, > > I have a tables having this structure: > > ID (int) | _ID (int) | Name (String) > > > _ID is the parent of ID. > > I'm looking for the best method to perform a cascade delete. For example, I > delete _ID, it in turn deletes ID, and should ID also be an entry within _ID, it > would continue through the chain. > > For example: > > 0 0 Base > 1 0 Sib1 > 2 0 Sib2 > 3 0 Sib3 > 4 1 Sib1_1 > 5 1 Sib1_2 > > > Deleting Base would remove all the entries. Deleting Sib1 would delete Sib1_1 > and Sib1_2. > Can anyone help out here? > > Regards, > Craig May > > Enth Dimension > http://www.enthdimension.com.au >
Hi, there, 2 ways may resolve you problem, I believe: 1. re_create your table set _ID as primary key and ID as foreign key with option ON DELETE CASCADE if _ID is unique key. 2. create a rule bind this table, for each row deleting(or create a trigger). Craig May wrote: > Hi, > > I have a tables having this structure: > > ID (int) | _ID (int) | Name (String) > > _ID is the parent of ID. > > I'm looking for the best method to perform a cascade delete. For example, I > delete _ID, it in turn deletes ID, and should ID also be an entry within _ID, it > would continue through the chain. > > For example: > > 0 0 Base > 1 0 Sib1 > 2 0 Sib2 > 3 0 Sib3 > 4 1 Sib1_1 > 5 1 Sib1_2 > > Deleting Base would remove all the entries. Deleting Sib1 would delete Sib1_1 > and Sib1_2. > Can anyone help out here? > > Regards, > Craig May > > Enth Dimension > http://www.enthdimension.com.au -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com
> 1. re_create your table set _ID as primary key This is not going to work unless a parent is prohibited from having more than one child. From the brief sample data supplied,this is clearly not the case. Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com