pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddelete on cascade
8+ messages / 5 participants
[nested] [flat]
* delete on cascade
@ 2006-10-23 08:49 Luca Ferrari <fluca1978@infinito.it>
0 siblings, 4 replies; 8+ messages in thread
From: Luca Ferrari @ 2006-10-23 08:49 UTC (permalink / raw)
To: pgsql-sql
Hi all,
I guess this is an already asked question, but I didn't found an answer, so
apologize me. Imagine I've got two tables:
skill(id,description) // primary key => id
family(id,description) // primary key => id
and I want to associate skills to families:
ass_sf(id_skill,id_family) // appropriate foreign keys
Tables are already created and the database is running. Now I'd like to
implement a delete cascade, thus when I delete a skill also its association
with the family must be deleted. I gues I should have declared the skill
table as follows:
CREATE TABLE skill
(
id varchar(20) on delete cascade,
description varchar(50),
primary key(id)
);
right? The problem is: how can I alter the table to add the column constraint
now, without redeclaring the table?
Thanks very much for helping me.
Luca
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: delete on cascade
@ 2006-10-23 09:00 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Luca Ferrari <fluca1978@infinito.it>
3 siblings, 1 reply; 8+ messages in thread
From: Achilleas Mantzios @ 2006-10-23 09:00 UTC (permalink / raw)
To: pgsql-sql
Óôéò ÄåõôÝñá 23 Ïêôþâñéïò 2006 11:49, ï/ç Luca Ferrari Ýãñáøå:
> Hi all,
> I guess this is an already asked question, but I didn't found an answer, so
> apologize me. Imagine I've got two tables:
> skill(id,description) // primary key => id
> family(id,description) // primary key => id
> and I want to associate skills to families:
> ass_sf(id_skill,id_family) // appropriate foreign keys
>
> Tables are already created and the database is running. Now I'd like to
> implement a delete cascade, thus when I delete a skill also its association
> with the family must be deleted. I gues I should have declared the skill
> table as follows:
> CREATE TABLE skill
> (
> id varchar(20) on delete cascade,
> description varchar(50),
> primary key(id)
> );
>
> right? The problem is: how can I alter the table to add the column
not right!!!
ON DELETE CASCADE is specified in FOREIGN KEY contsraints.
So that would be in ass_sf table.
If you find ALTER TABLE ass_sf command hard to run, then drob your
ass_sf table and define it like
CREATE TABLE ass_sf (
.....
skill_id INT REFERENCES skill(id) ON DELETE CASCADE,
familly_id INT REFERENCES family(id) ON DELETE CASCADE
);
The above causes cascade deletes not only on skills but on families also.
> constraint now, without redeclaring the table?
> Thanks very much for helping me.
> Luca
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: delete on cascade
@ 2006-10-23 09:50 Luca Ferrari <fluca1978@infinito.it>
parent: Achilleas Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Luca Ferrari @ 2006-10-23 09:50 UTC (permalink / raw)
To: pgsql-sql
On Monday 23 October 2006 11:00 Achilleas Mantzios's cat, walking on the
keyboard, wrote:
> not right!!!
> ON DELETE CASCADE is specified in FOREIGN KEY contsraints.
> So that would be in ass_sf table.
> If you find ALTER TABLE ass_sf command hard to run, then drob your
> ass_sf table and define it like
>
Sorry, but this would define that when I delete a row on ass_sf also the skin
and the family will be deleted, right? I would like to get the opposite
behaviour: when I delete a skill also its association on ass_sf will be
deleted. Is this possible?
Thanks,
Luca
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: delete on cascade
@ 2006-10-23 10:28 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Luca Ferrari <fluca1978@infinito.it>
0 siblings, 0 replies; 8+ messages in thread
From: Achilleas Mantzios @ 2006-10-23 10:28 UTC (permalink / raw)
To: pgsql-sql
Óôéò ÄåõôÝñá 23 Ïêôþâñéïò 2006 12:50, ï/ç Luca Ferrari Ýãñáøå:
> On Monday 23 October 2006 11:00 Achilleas Mantzios's cat, walking on the
>
> keyboard, wrote:
> > not right!!!
> > ON DELETE CASCADE is specified in FOREIGN KEY contsraints.
> > So that would be in ass_sf table.
> > If you find ALTER TABLE ass_sf command hard to run, then drob your
> > ass_sf table and define it like
>
> Sorry, but this would define that when I delete a row on ass_sf also the
> skin and the family will be deleted, right?
Not right. This would specify quite the opposite.
> I would like to get the
> opposite behaviour: when I delete a skill also its association on ass_sf
> will be deleted. Is this possible?
Absolutely, and this is done in the way i just described.
In any case I would recommend reading the docs.
>
> Thanks,
> Luca
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: delete on cascade
@ 2006-10-23 15:41 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Luca Ferrari <fluca1978@infinito.it>
3 siblings, 0 replies; 8+ messages in thread
From: Richard Broersma Jr @ 2006-10-23 15:41 UTC (permalink / raw)
To: Luca Ferrari <fluca1978@infinito.it>; pgsql-sql
> Hi all,
> I guess this is an already asked question, but I didn't found an answer, so
> apologize me. Imagine I've got two tables:
> skill(id,description) // primary key => id
> family(id,description) // primary key => id
> and I want to associate skills to families:
> ass_sf(id_skill,id_family) // appropriate foreign keys
>
> Tables are already created and the database is running. Now I'd like to
> implement a delete cascade, thus when I delete a skill also its association
> with the family must be deleted. I gues I should have declared the skill
> table as follows:
> CREATE TABLE skill
> (
> id varchar(20) on delete cascade,
> description varchar(50),
> primary key(id)
> );
>
> right? The problem is: how can I alter the table to add the column constraint
> now, without redeclaring the table?
> Thanks very much for helping me.
This link has one line that is very similar to what you want to do. You will probably have to
start a transaction, drop the foriegn key contraint and then add a new foriegn key contraint with
the on drop cascade .
http://archives.postgresql.org/pgsql-general/2006-10/msg00467.php
Regards,
Richard Broersma Jr.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: delete on cascade
@ 2006-10-23 15:41 chester c young <chestercyoung@yahoo.com>
parent: Luca Ferrari <fluca1978@infinito.it>
3 siblings, 0 replies; 8+ messages in thread
From: chester c young @ 2006-10-23 15:41 UTC (permalink / raw)
To: Luca Ferrari <fluca1978@infinito.it>; +Cc: pgsql-sql
--- Luca Ferrari <fluca1978@infinito.it> wrote:
> Hi all,
> I guess this is an already asked question, but I didn't found an
> answer, so
> apologize me. Imagine I've got two tables:
> skill(id,description) // primary key => id
> family(id,description) // primary key => id
> and I want to associate skills to families:
> ass_sf(id_skill,id_family) // appropriate foreign keys
>
> Tables are already created and the database is running. Now I'd like
> to
> implement a delete cascade, thus when I delete a skill also its
> association
> with the family must be deleted. I gues I should have declared the
> skill
> table as follows:
> CREATE TABLE skill
> (
> id varchar(20) on delete cascade,
> description varchar(50),
> primary key(id)
> );
>
> right? The problem is: how can I alter the table to add the column
> constraint
> now, without redeclaring the table?
> Thanks very much for helping me.
> Luca
drop the constraint and re-declare it. (you can see the constraint
name by \dt skill from psql.)
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: delete on cascade
@ 2006-10-23 15:48 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Luca Ferrari <fluca1978@infinito.it>
3 siblings, 0 replies; 8+ messages in thread
From: Stephan Szabo @ 2006-10-23 15:48 UTC (permalink / raw)
To: Luca Ferrari <fluca1978@infinito.it>; +Cc: pgsql-sql
On Mon, 23 Oct 2006, Luca Ferrari wrote:
> Hi all,
> I guess this is an already asked question, but I didn't found an answer, so
> apologize me. Imagine I've got two tables:
> skill(id,description) // primary key => id
> family(id,description) // primary key => id
> and I want to associate skills to families:
> ass_sf(id_skill,id_family) // appropriate foreign keys
Using the type information from the original and assuming it's the same
for family, without referential actions that'd look something like:
Create table skill(id varchar(20) primary key, description varchar(50));
Create table family(id varchar(20) primary key, description varchar(50));
Create table ass_sf(id_skill varchar(20) references skill,
id_family varchar(20) referenced family);
> Tables are already created and the database is running. Now I'd like to
> implement a delete cascade, thus when I delete a skill also its association
> with the family must be deleted.
In this case, you'd need to remove the constraint on ass_sf.id_skill and
replace it with one like foreign key(id_skill) references skill on delete
cascade which you can do with alter table in two steps. The on delete
information is associated with the foreign key and deletes matching rows
from the referencing table (ass_sf) when rows in the referenced tablen
(skill) are deleted.
You may also want to think about what the on update behavior should be.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: delete on cascade
@ 2006-10-23 19:04 Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Richard Broersma Jr @ 2006-10-23 19:04 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; Luca Ferrari <fluca1978@infinito.it>; pgsql-sql
i guess my first attempt to send a reply failed.
--- Richard Broersma Jr <rabroersma@yahoo.com> wrote:
> > Hi all,
> > I guess this is an already asked question, but I didn't found an answer, so
> > apologize me. Imagine I've got two tables:
> > skill(id,description) // primary key => id
> > family(id,description) // primary key => id
> > and I want to associate skills to families:
> > ass_sf(id_skill,id_family) // appropriate foreign keys
> >
> > Tables are already created and the database is running. Now I'd like to
> > implement a delete cascade, thus when I delete a skill also its association
> > with the family must be deleted. I gues I should have declared the skill
> > table as follows:
> > CREATE TABLE skill
> > (
> > id varchar(20) on delete cascade,
> > description varchar(50),
> > primary key(id)
> > );
> >
> > right? The problem is: how can I alter the table to add the column constraint
> > now, without redeclaring the table?
> > Thanks very much for helping me.
>
> This link has one line that is very similar to what you want to do. You will probably have to
> start a transaction, drop the foriegn key contraint and then add a new foriegn key contraint
> with
> the on drop cascade .
>
> http://archives.postgresql.org/pgsql-general/2006-10/msg00467.php
>
> Regards,
>
> Richard Broersma Jr.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-10-23 19:04 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-23 08:49 delete on cascade Luca Ferrari <fluca1978@infinito.it>
2006-10-23 09:00 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2006-10-23 09:50 ` Luca Ferrari <fluca1978@infinito.it>
2006-10-23 10:28 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
2006-10-23 15:41 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-10-23 15:41 ` chester c young <chestercyoung@yahoo.com>
2006-10-23 15:48 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2006-10-23 19:04 Re: delete on cascade Richard Broersma Jr <rabroersma@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox