Thread: delete on cascade
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
Στις Δευτέρα 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
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
Στις Δευτέρα 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
--- 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
> 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.
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.
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. > >
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
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