Thread: cascade delete

cascade delete

From
Bhushan Bhangale
Date:
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
________________________________________________________________________

Re: cascade delete

From
Dave Cramer
Date:
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


Re: cascade delete

From
Bhushan Bhangale
Date:
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
________________________________________________________________________

Re: cascade delete

From
Dave Cramer
Date:
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


Re: cascade delete

From
Bhushan Bhangale
Date:
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
________________________________________________________________________

Re: cascade delete

From
Bhushan Bhangale
Date:
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
________________________________________________________________________

Re: cascade delete

From
Dave Cramer
Date:
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


Re: cascade delete

From
Andrew Rawnsley
Date:
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


Re: cascade delete

From
Bhushan Bhangale
Date:
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
________________________________________________________________________

Re: cascade delete

From
"Chris Smith"
Date:
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