Thread: cascade delete
Hi,
I have following 2 tables in postgres DB. I created a cascade delete
constraint on image_id column of accomm_images which is a foreign key from
image tbale.
When I execute -> delete from accomm_images
It should delete all the child records of image table as well. But its not
happening.
CREATE TABLE image(
id int4 NOT NULL,
url int4 NOT NULL
);
CREATE TABLE accomm_images(
accomm_id int4 NOT NULL references accomm(id),
image_id int4 NOT NULL references image(id) on delete
cascade
);
Is there anything missing?
Thanks
Bhushan
________________________________________________________________________
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
________________________________________________________________________
Does this work in psql? The driver wouldn't do anything to interfere with this. Dave On Mon, 2004-02-16 at 09:49, Bhushan Bhangale wrote: > Hi, > > I have following 2 tables in postgres DB. I created a cascade delete > constraint on image_id column of accomm_images which is a foreign key from > image tbale. > > When I execute -> delete from accomm_images > It should delete all the child records of image table as well. But its not > happening. > > CREATE TABLE image( > id int4 NOT NULL, > url int4 NOT NULL > ); > > CREATE TABLE accomm_images( > accomm_id int4 NOT NULL references accomm(id), > image_id int4 NOT NULL references image(id) on delete > cascade > ); > > > Is there anything missing? > > Thanks > Bhushan > > ________________________________________________________________________ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ________________________________________________________________________ > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Dave Cramer 519 939 0336 ICQ # 14675561
Well I did google and got that delete cascade DDL for postgres. I tried it using odbc and jdbc but didn't worked. -----Original Message----- From: Dave Cramer [mailto:pg@fastcrypt.com] Sent: 16 February 2004 15:03 To: Bhushan Bhangale Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] cascade delete Does this work in psql? The driver wouldn't do anything to interfere with this. Dave On Mon, 2004-02-16 at 09:49, Bhushan Bhangale wrote: > Hi, > > I have following 2 tables in postgres DB. I created a cascade delete > constraint on image_id column of accomm_images which is a foreign key from > image tbale. > > When I execute -> delete from accomm_images > It should delete all the child records of image table as well. But its not > happening. > > CREATE TABLE image( > id int4 NOT NULL, > url int4 NOT NULL > ); > > CREATE TABLE accomm_images( > accomm_id int4 NOT NULL references accomm(id), > image_id int4 NOT NULL references image(id) on delete > cascade > ); > > > Is there anything missing? > > Thanks > Bhushan > > ________________________________________________________________________ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > ________________________________________________________________________ > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Dave Cramer 519 939 0336 ICQ # 14675561 ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
It works the other way around, If you delete from images, then the corresponding entry from accomm_images will be deleted. DAve On Mon, 2004-02-16 at 10:33, Bhushan Bhangale wrote: > Well I did google and got that delete cascade DDL for postgres. I tried it > using odbc and jdbc but didn't worked. > > -----Original Message----- > From: Dave Cramer [mailto:pg@fastcrypt.com] > Sent: 16 February 2004 15:03 > To: Bhushan Bhangale > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] cascade delete > > > Does this work in psql? The driver wouldn't do anything to interfere > with this. > > Dave > On Mon, 2004-02-16 at 09:49, Bhushan Bhangale wrote: > > Hi, > > > > I have following 2 tables in postgres DB. I created a cascade delete > > constraint on image_id column of accomm_images which is a foreign key from > > image tbale. > > > > When I execute -> delete from accomm_images > > It should delete all the child records of image table as well. But its not > > happening. > > > > CREATE TABLE image( > > id int4 NOT NULL, > > url int4 NOT NULL > > ); > > > > CREATE TABLE accomm_images( > > accomm_id int4 NOT NULL references accomm(id), > > image_id int4 NOT NULL references image(id) on delete > > cascade > > ); > > > > > > Is there anything missing? > > > > Thanks > > Bhushan > > > > ________________________________________________________________________ > > This e-mail has been scanned for all viruses by Star Internet. The > > service is powered by MessageLabs. For more information on a proactive > > anti-virus service working around the clock, around the globe, visit: > > http://www.star.net.uk > > ________________________________________________________________________ > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > -- Dave Cramer 519 939 0336 ICQ # 14675561
I want other way round. -----Original Message----- From: Dave Cramer [mailto:pg@fastcrypt.com] Sent: 16 February 2004 15:51 To: Bhushan Bhangale Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] cascade delete It works the other way around, If you delete from images, then the corresponding entry from accomm_images will be deleted. DAve On Mon, 2004-02-16 at 10:33, Bhushan Bhangale wrote: > Well I did google and got that delete cascade DDL for postgres. I tried it > using odbc and jdbc but didn't worked. > > -----Original Message----- > From: Dave Cramer [mailto:pg@fastcrypt.com] > Sent: 16 February 2004 15:03 > To: Bhushan Bhangale > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] cascade delete > > > Does this work in psql? The driver wouldn't do anything to interfere > with this. > > Dave > On Mon, 2004-02-16 at 09:49, Bhushan Bhangale wrote: > > Hi, > > > > I have following 2 tables in postgres DB. I created a cascade delete > > constraint on image_id column of accomm_images which is a foreign key from > > image tbale. > > > > When I execute -> delete from accomm_images > > It should delete all the child records of image table as well. But its not > > happening. > > > > CREATE TABLE image( > > id int4 NOT NULL, > > url int4 NOT NULL > > ); > > > > CREATE TABLE accomm_images( > > accomm_id int4 NOT NULL references accomm(id), > > image_id int4 NOT NULL references image(id) on delete > > cascade > > ); > > > > > > Is there anything missing? > > > > Thanks > > Bhushan > > > > ________________________________________________________________________ > > This e-mail has been scanned for all viruses by Star Internet. The > > service is powered by MessageLabs. For more information on a proactive > > anti-virus service working around the clock, around the globe, visit: > > http://www.star.net.uk > > ________________________________________________________________________ > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 8: explain analyze is your friend > > -- Dave Cramer 519 939 0336 ICQ # 14675561 ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
But the image table is the master table where all the entires on image is done. The accomm_images table has mapping of accomm_id and images. Now I want to delete images of a accomm_id. So I delete records from accomm_images table based on that accomm_id and want the images to be delted from image table. -----Original Message----- From: Andrew Rawnsley [mailto:ronz@ravensfield.com] Sent: 16 February 2004 15:58 To: Bhushan Bhangale Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] cascade delete If I am understanding properly what you want, you have your referential integrity backwards. If you want everything from table image to be removed when you execute 'delete from accomm_images' your table definition should be: > CREATE TABLE accomm_images( > accomm_id int4 NOT NULL references accomm(id), > image_id int4 NOT NULL > ); > CREATE TABLE image( > id int4 NOT NULL references accomm_images(image_id) on > delete cascade, > url int4 NOT NULL > ); The reference goes from child to parent, not parent to child, and from what you say the image table is the child and accomm_images is the parent. On Feb 16, 2004, at 9:49 AM, Bhushan Bhangale wrote: > Hi, > > I have following 2 tables in postgres DB. I created a cascade delete > constraint on image_id column of accomm_images which is a foreign key > from > image tbale. > > When I execute -> delete from accomm_images > It should delete all the child records of image table as well. But its > not > happening. > > CREATE TABLE image( > id int4 NOT NULL, > url int4 NOT NULL > ); > > CREATE TABLE accomm_images( > accomm_id int4 NOT NULL references accomm(id), > image_id int4 NOT NULL references image(id) on delete > cascade > ); > > > Is there anything missing? > > Thanks > Bhushan > > _______________________________________________________________________ > _ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > _______________________________________________________________________ > _ > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > -------------------- Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
Which one is the parent table? If accomm_images is the parent, then make id reference it and cascade delete. Dave On Mon, 2004-02-16 at 10:53, Bhushan Bhangale wrote: > I want other way round. > > -----Original Message----- > From: Dave Cramer [mailto:pg@fastcrypt.com] > Sent: 16 February 2004 15:51 > To: Bhushan Bhangale > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] cascade delete > > > It works the other way around, > > If you delete from images, then the corresponding entry from > accomm_images will be deleted. > > > DAve > On Mon, 2004-02-16 at 10:33, Bhushan Bhangale wrote: > > Well I did google and got that delete cascade DDL for postgres. I tried it > > using odbc and jdbc but didn't worked. > > > > -----Original Message----- > > From: Dave Cramer [mailto:pg@fastcrypt.com] > > Sent: 16 February 2004 15:03 > > To: Bhushan Bhangale > > Cc: pgsql-jdbc@postgresql.org > > Subject: Re: [JDBC] cascade delete > > > > > > Does this work in psql? The driver wouldn't do anything to interfere > > with this. > > > > Dave > > On Mon, 2004-02-16 at 09:49, Bhushan Bhangale wrote: > > > Hi, > > > > > > I have following 2 tables in postgres DB. I created a cascade delete > > > constraint on image_id column of accomm_images which is a foreign key > from > > > image tbale. > > > > > > When I execute -> delete from accomm_images > > > It should delete all the child records of image table as well. But its > not > > > happening. > > > > > > CREATE TABLE image( > > > id int4 NOT NULL, > > > url int4 NOT NULL > > > ); > > > > > > CREATE TABLE accomm_images( > > > accomm_id int4 NOT NULL references accomm(id), > > > image_id int4 NOT NULL references image(id) on delete > > > cascade > > > ); > > > > > > > > > Is there anything missing? > > > > > > Thanks > > > Bhushan > > > > > > ________________________________________________________________________ > > > This e-mail has been scanned for all viruses by Star Internet. The > > > service is powered by MessageLabs. For more information on a proactive > > > anti-virus service working around the clock, around the globe, visit: > > > http://www.star.net.uk > > > ________________________________________________________________________ > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 8: explain analyze is your friend > > > -- Dave Cramer 519 939 0336 ICQ # 14675561
If I am understanding properly what you want, you have your referential integrity backwards. If you want everything from table image to be removed when you execute 'delete from accomm_images' your table definition should be: > CREATE TABLE accomm_images( > accomm_id int4 NOT NULL references accomm(id), > image_id int4 NOT NULL > ); > CREATE TABLE image( > id int4 NOT NULL references accomm_images(image_id) on > delete cascade, > url int4 NOT NULL > ); The reference goes from child to parent, not parent to child, and from what you say the image table is the child and accomm_images is the parent. On Feb 16, 2004, at 9:49 AM, Bhushan Bhangale wrote: > Hi, > > I have following 2 tables in postgres DB. I created a cascade delete > constraint on image_id column of accomm_images which is a foreign key > from > image tbale. > > When I execute -> delete from accomm_images > It should delete all the child records of image table as well. But its > not > happening. > > CREATE TABLE image( > id int4 NOT NULL, > url int4 NOT NULL > ); > > CREATE TABLE accomm_images( > accomm_id int4 NOT NULL references accomm(id), > image_id int4 NOT NULL references image(id) on delete > cascade > ); > > > Is there anything missing? > > Thanks > Bhushan > > _______________________________________________________________________ > _ > This e-mail has been scanned for all viruses by Star Internet. The > service is powered by MessageLabs. For more information on a proactive > anti-virus service working around the clock, around the globe, visit: > http://www.star.net.uk > _______________________________________________________________________ > _ > > ---------------------------(end of > broadcast)--------------------------- > TIP 8: explain analyze is your friend > -------------------- Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com
parent table is image -----Original Message----- From: Dave Cramer [mailto:pg@fastcrypt.com] Sent: 16 February 2004 16:04 To: Bhushan Bhangale Cc: pgsql-jdbc@postgresql.org Subject: RE: [JDBC] cascade delete Which one is the parent table? If accomm_images is the parent, then make id reference it and cascade delete. Dave On Mon, 2004-02-16 at 10:53, Bhushan Bhangale wrote: > I want other way round. > > -----Original Message----- > From: Dave Cramer [mailto:pg@fastcrypt.com] > Sent: 16 February 2004 15:51 > To: Bhushan Bhangale > Cc: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] cascade delete > > > It works the other way around, > > If you delete from images, then the corresponding entry from > accomm_images will be deleted. > > > DAve > On Mon, 2004-02-16 at 10:33, Bhushan Bhangale wrote: > > Well I did google and got that delete cascade DDL for postgres. I tried it > > using odbc and jdbc but didn't worked. > > > > -----Original Message----- > > From: Dave Cramer [mailto:pg@fastcrypt.com] > > Sent: 16 February 2004 15:03 > > To: Bhushan Bhangale > > Cc: pgsql-jdbc@postgresql.org > > Subject: Re: [JDBC] cascade delete > > > > > > Does this work in psql? The driver wouldn't do anything to interfere > > with this. > > > > Dave > > On Mon, 2004-02-16 at 09:49, Bhushan Bhangale wrote: > > > Hi, > > > > > > I have following 2 tables in postgres DB. I created a cascade delete > > > constraint on image_id column of accomm_images which is a foreign key > from > > > image tbale. > > > > > > When I execute -> delete from accomm_images > > > It should delete all the child records of image table as well. But its > not > > > happening. > > > > > > CREATE TABLE image( > > > id int4 NOT NULL, > > > url int4 NOT NULL > > > ); > > > > > > CREATE TABLE accomm_images( > > > accomm_id int4 NOT NULL references accomm(id), > > > image_id int4 NOT NULL references image(id) on delete > > > cascade > > > ); > > > > > > > > > Is there anything missing? > > > > > > Thanks > > > Bhushan > > > > > > ________________________________________________________________________ > > > This e-mail has been scanned for all viruses by Star Internet. The > > > service is powered by MessageLabs. For more information on a proactive > > > anti-virus service working around the clock, around the globe, visit: > > > http://www.star.net.uk > > > ________________________________________________________________________ > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 8: explain analyze is your friend > > > -- Dave Cramer 519 939 0336 ICQ # 14675561 ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________
Bhushan Bhangale wrote: > parent table is image Then your requirements are different from those solved by ON DELETE CASCADE. You can still write a trigger to do what you want, however. -- www.designacourse.com The Easiest Way to Train Anyone... Anywhere. Chris Smith - Lead Software Developer/Technical Trainer MindIQ Corporation