Thread: delete on cascade

delete on cascade

From
Luca Ferrari
Date:
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


Re: delete on cascade

From
Achilleas Mantzios
Date:
Στις Δευτέρα 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


Re: delete on cascade

From
Luca Ferrari
Date:
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


Re: delete on cascade

From
Achilleas Mantzios
Date:
Στις Δευτέρα 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


Re: delete on cascade

From
chester c young
Date:
--- 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 


Re: delete on cascade

From
Richard Broersma Jr
Date:
> 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.



Re: delete on cascade

From
Stephan Szabo
Date:
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.


Re: delete on cascade

From
Richard Broersma Jr
Date:
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.
> 
> 



Function to reset sequence.....

From
"Doug Hyde"
Date:
I am a real newbie with PL/pgSQL, and with postgres, but here goes. 

The problem: when you import data into postgresql, there is no way to set
the proper sequence on the primary key. For example, I could have a table
with 2000 rows, some of which have been added/deleted in time, so the
nextval for the sequence should be 3301. When you set the table up with sql
as:

CREATE TABLE "public"."tblcompany"(
"intcompany" SERIAL PRIMARY KEY,
"txtcompanyname_1" varchar(255) ,
...
);

The nextval is set by default to 1. 

To overcome this, and avoid manually resetting the key (which is error
prone), my strategy is (1) create the structure, (2) import the data, and
then (3) reset the primary key. I have written sql to accomplish the first
two which I have working well; the third is more complicated. What I would
like to try is pasted below, after messing around - I haven't really got it
even close to working:

CREATE OR REPLACE FUNCTION fixsequence() returns void AS $$

BEGIN
 x RECORD;  temp int;


-- set up a loop for the tables in the database
 FOR x INSELECT table_name, column_nameFROM information_schema.key_column_usageNATURAL JOIN
information_schema.table_constraintsWHEREconstraint_type = 'PRIMARY KEY'AND ordinal_position = 1ORDER BY 1;
 

-- begin loop 
 LOOP-- get the max value of the primary key and add 1select max(x.column_name)+1 as temp from x.tablename;
-- get the seqence name for the table, sequence name always includes
the table name of the pimary keyselect relname as seq_name  from pg_class where relkind = 'S' and
relname like x.table_name'%';
-- now reset the sequence for that tableSELECT setval(seq_name, temp); END LOOP;

END;
$LANGUAGE 'plpgsql';

Before I mess up my data, will this (or something like it work) as I have
little confidence? I am having trouble with combining variables with
wildcards (middle of the loop).  

Thanks for any support. 

Doug



Re: Function to reset sequence.....

From
"Anonymous"
Date:
It is not the answer to your question, but please note that the NATURAL
JOIN may cause problems if the underlaying database structure changes
(for example after a version upgrade). See the following thread for
more information:
http://forums.oracle.com/forums/thread.jspa?threadID=440287