From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Luca Ferrari <fluca1978@infinito.it>
Cc: pgsql-sql@postgresql.org
Subject: Re: delete on cascade
Date: Mon, 23 Oct 2006 08:48:15 -0700 (PDT)
Message-ID: <20061023084359.V59438@megazone.bigpanda.com> (raw)
In-Reply-To: <200610231049.16507.fluca1978@infinito.it>
References: <200610231049.16507.fluca1978@infinito.it>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, fluca1978@infinito.it
Subject: Re: delete on cascade
In-Reply-To: <20061023084359.V59438@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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