pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedOn delete cascade!
10+ messages / 8 participants
[nested] [flat]
* On delete cascade!
@ 2002-01-14 20:31 Paulo J. Matos <pocm@rnl.ist.utl.pt>
0 siblings, 3 replies; 10+ messages in thread
From: pocm@rnl.ist.utl.pt @ 2002-01-14 20:31 UTC (permalink / raw)
To: pgsql-sql
Hi all,
On weak entities I've used ON DELETE CASCADE as mentioned in
"Databases Management Systems" by Raghu Ramakrishnan. But I get:
psql:tables.sql:43: ERROR: parser: parse error at or near "ON"
on tables like the following:
CREATE TABLE socio_ilumindado (
cognome varchar,
id integer,
nome varchar,
alcunha varchar,
data_filiacao date,
PRIMARY KEY(id),
FOREIGN KEY(id, nome, alcunha, data_filiacao) REFERENCES socio,
ON DELETE CASCADE);
How can I change this to be compatible with postgresql?
TIA,
Best regards,
--
Paulo J. Matos : pocm(_at_)rnl.ist.utl.pt
Instituto Superior Tecnico - Lisbon
Software & Computer Engineering - A.I.
- > http://www.rnl.ist.utl.pt/~pocm
---
Yes, God had a deadline...
So, He wrote it all in Lisp!
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: On delete cascade!
@ 2002-01-14 23:53 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Paulo J. Matos <pocm@rnl.ist.utl.pt>
2 siblings, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2002-01-14 23:53 UTC (permalink / raw)
To: Paulo J. Matos <pocm@rnl.ist.utl.pt>; +Cc: pgsql-sql
On 14 Jan 2002, Paulo J. Matos wrote:
> On weak entities I've used ON DELETE CASCADE as mentioned in
> "Databases Management Systems" by Raghu Ramakrishnan. But I get:
> psql:tables.sql:43: ERROR: parser: parse error at or near "ON"
>
> on tables like the following:
>
> CREATE TABLE socio_ilumindado (
> cognome varchar,
> id integer,
> nome varchar,
> alcunha varchar,
> data_filiacao date,
> PRIMARY KEY(id),
> FOREIGN KEY(id, nome, alcunha, data_filiacao) REFERENCES socio,
> ON DELETE CASCADE);
>
>
> How can I change this to be compatible with postgresql?
We don't want the comma between socio and the ON DELETE CASCADE (which as
far as I can see isn't allowed in the SQL92 spec either, but I may be
missing it).
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: On delete cascade!
@ 2002-01-15 06:29 Roberto Mello <rmello@cc.usu.edu>
parent: Paulo J. Matos <pocm@rnl.ist.utl.pt>
2 siblings, 1 reply; 10+ messages in thread
From: Roberto Mello @ 2002-01-15 06:29 UTC (permalink / raw)
To: Paulo J. Matos <pocm@rnl.ist.utl.pt>; +Cc: pgsql-sql
On Mon, Jan 14, 2002 at 08:31:36PM +0000, Paulo J. Matos wrote:
> Hi all,
>
> On weak entities I've used ON DELETE CASCADE as mentioned in
> "Databases Management Systems" by Raghu Ramakrishnan. But I get:
> psql:tables.sql:43: ERROR: parser: parse error at or near "ON"
>
> on tables like the following:
>
> CREATE TABLE socio_ilumindado (
> cognome varchar,
> id integer,
> nome varchar,
> alcunha varchar,
> data_filiacao date,
> PRIMARY KEY(id),
> FOREIGN KEY(id, nome, alcunha, data_filiacao) REFERENCES socio,
> ON DELETE CASCADE);
Ola Paulo,
You need to take a look at the PostgreSQL documentation as well. Just
reading your college databases book won't cut it most of the time.
PostgreSQL has excellent documentation, especially the reference manual.
Take a look at it. So far both of your questions could have been answered
by taking a quick glance at the docs.
http://www.postgresql.org/idocs/
-Roberto
--
+----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
Information is the greatest weapon of power to the modern wizard.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: On delete cascade!
@ 2002-01-15 11:32 Steve Boyle (Roselink) <boylesa@roselink.co.uk>
parent: Roberto Mello <rmello@cc.usu.edu>
0 siblings, 0 replies; 10+ messages in thread
From: Steve Boyle \(Roselink\) @ 2002-01-15 11:32 UTC (permalink / raw)
To: Roberto Mello <rmello@cc.usu.edu>; Paulo J. Matos <pocm@rnl.ist.utl.pt>; +Cc: pgsql-sql
Is the , after socio the problem here?
sb
----- Original Message -----
From: "Roberto Mello" <rmello@cc.usu.edu>
To: "Paulo J. Matos" <pocm@rnl.ist.utl.pt>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, January 15, 2002 6:29 AM
Subject: Re: [SQL] On delete cascade!
> On Mon, Jan 14, 2002 at 08:31:36PM +0000, Paulo J. Matos wrote:
> > Hi all,
> >
> > On weak entities I've used ON DELETE CASCADE as mentioned in
> > "Databases Management Systems" by Raghu Ramakrishnan. But I get:
> > psql:tables.sql:43: ERROR: parser: parse error at or near "ON"
> >
> > on tables like the following:
> >
> > CREATE TABLE socio_ilumindado (
> > cognome varchar,
> > id integer,
> > nome varchar,
> > alcunha varchar,
> > data_filiacao date,
> > PRIMARY KEY(id),
> > FOREIGN KEY(id, nome, alcunha, data_filiacao) REFERENCES socio,
> > ON DELETE CASCADE);
>
> Ola Paulo,
>
> You need to take a look at the PostgreSQL documentation as well. Just
> reading your college databases book won't cut it most of the time.
>
> PostgreSQL has excellent documentation, especially the reference manual.
> Take a look at it. So far both of your questions could have been answered
> by taking a quick glance at the docs.
>
> http://www.postgresql.org/idocs/
>
> -Roberto
>
> --
> +----| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |------+
> Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
> http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
> Information is the greatest weapon of power to the modern wizard.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: On delete cascade!
@ 2002-02-26 07:53 Erle Czar Mantos <erle@hindang.msuiit.edu.ph>
parent: Paulo J. Matos <pocm@rnl.ist.utl.pt>
2 siblings, 0 replies; 10+ messages in thread
From: Erle Czar Mantos @ 2002-02-26 07:53 UTC (permalink / raw)
To: Paulo J. Matos <pocm@rnl.ist.utl.pt>; +Cc: pgsql-sql
To You:
The problem is on the comman after the word "socio", it shouldn't
have been there!
Erle
On 14 Jan 2002, Paulo J. Matos wrote:
> Hi all,
>
> On weak entities I've used ON DELETE CASCADE as mentioned in
> "Databases Management Systems" by Raghu Ramakrishnan. But I get:
> psql:tables.sql:43: ERROR: parser: parse error at or near "ON"
>
> on tables like the following:
>
> CREATE TABLE socio_ilumindado (
> cognome varchar,
> id integer,
> nome varchar,
> alcunha varchar,
> data_filiacao date,
> PRIMARY KEY(id),
> FOREIGN KEY(id, nome, alcunha, data_filiacao) REFERENCES socio,
> ON DELETE CASCADE);
>
>
> How can I change this to be compatible with postgresql?
>
> TIA,
> Best regards,
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* ON DELETE CASCADE
@ 2002-12-12 17:01 Tim Perdue <tim@perdue.net>
0 siblings, 3 replies; 10+ messages in thread
From: Tim Perdue @ 2002-12-12 17:01 UTC (permalink / raw)
To: pgsql-sql
I'm trying to comb through my database and add ON DELETE CASCADE to a
number of tables where I already have fkeys in place, but I'm having a
hard time.
ALTER TABLE project_task DROP CONSTRAINT
"project_task_group_project_id_f" RESTRICT;
ERROR: ALTER TABLE / DROP CONSTRAINT: project_task_group_project_id_f
does not exist
ALTER TABLE project_task
ADD CONSTRAINT projecttask_groupprojectid_fk
FOREIGN KEY (group_project_id)
REFERENCES project_group_list(group_project_id) ON DELETE CASCADE;
NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ALTER
That command works, but now I think I have 2x as many triggers as I
want. How do I get rid of the original triggers?
alexandria=# \d project_task
Table "project_task"
Column | Type | Modifiers
------------------+------------------+-----------------------
project_task_id | integer | not null default
nextval('project_task_pk_seq'::text)
group_project_id | integer | not null default '0'
summary | text | not null default ''
details | text | not null default ''
percent_complete | integer | not null default '0'
priority | integer | not null default '0'
hours | double precision | not null default '0.00'
start_date | integer | not null default '0'
end_date | integer | not null default '0'
created_by | integer | not null default '0'
status_id | integer | not null default '0'
Indexes: projecttask_projid_status
Primary key: project_task_pkey
Triggers: RI_ConstraintTrigger_51030049,
RI_ConstraintTrigger_51030047,
RI_ConstraintTrigger_4305858,
RI_ConstraintTrigger_4305852,
RI_ConstraintTrigger_4305846
After adding the new foreign key:
Triggers: RI_ConstraintTrigger_51364957, ***new
RI_ConstraintTrigger_51030049,
RI_ConstraintTrigger_51030047,
RI_ConstraintTrigger_4305858,
RI_ConstraintTrigger_4305852,
RI_ConstraintTrigger_4305846
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ON DELETE CASCADE
@ 2002-12-12 17:11 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Tim Perdue <tim@perdue.net>
2 siblings, 0 replies; 10+ messages in thread
From: Stephan Szabo @ 2002-12-12 17:11 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-sql
On Thu, 12 Dec 2002, Tim Perdue wrote:
> I'm trying to comb through my database and add ON DELETE CASCADE to a
> number of tables where I already have fkeys in place, but I'm having a
> hard time.
>
> ALTER TABLE project_task DROP CONSTRAINT
> "project_task_group_project_id_f" RESTRICT;
>
> ERROR: ALTER TABLE / DROP CONSTRAINT: project_task_group_project_id_f
> does not exist
I think this works in 7.3, but that's the first version in which it does.
> ALTER TABLE project_task
> ADD CONSTRAINT projecttask_groupprojectid_fk
> FOREIGN KEY (group_project_id)
> REFERENCES project_group_list(group_project_id) ON DELETE CASCADE;
> NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ALTER
>
> That command works, but now I think I have 2x as many triggers as I
> want. How do I get rid of the original triggers?
You'll need to look at pg_trigger and find the triggers associated with
the constraints. I'd suggest removing both sets and then using alter
table again because then you can look for triggers that have the correct
tables and columns listed in tgargs. As a note there are two triggers on
project_group_list for each constraint as well and you'll need to remove
those too.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ON DELETE CASCADE
@ 2002-12-12 17:26 Marie G. Tuite <marie.tuite@edisonaffiliates.com>
parent: Tim Perdue <tim@perdue.net>
2 siblings, 1 reply; 10+ messages in thread
From: Marie G. Tuite @ 2002-12-12 17:26 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; pgsql-sql
Here is a function that I use to list fk(triggers) on a table. Execute the
function to get the trigger name and then - drop trigger "trigger_name" on
table_name;
hth
-- Version 1.0, June 2002
-- Marie G. Tuite
-- Function lists FK by table.
-- To execute:
-- begin;select fn_list_fk('table_name');
-- fetch all from "<cursor_name>"; end;
create or replace function fn_list_fk(name)
returns refcursor as
'
declare
table_in alias for $1;
rc refcursor;
begin
open rc for
select tgname as trigger_name_sys
,tgconstrname as trigger_name_given
,b.relname as table_name
,tgisconstraint as ri
,c.relname as parent_table
from pg_trigger a,
pg_class b,
pg_class c
where a.tgrelid=b.oid and b.relname=table_in
and tgconstrrelid = c.oid;
return rc;
end;
'
language 'plpgsql'
;
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Tim Perdue
> Sent: Thursday, December 12, 2002 11:01 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] ON DELETE CASCADE
>
>
> I'm trying to comb through my database and add ON DELETE CASCADE to a
> number of tables where I already have fkeys in place, but I'm having a
> hard time.
>
> ALTER TABLE project_task DROP CONSTRAINT
> "project_task_group_project_id_f" RESTRICT;
>
> ERROR: ALTER TABLE / DROP CONSTRAINT: project_task_group_project_id_f
> does not exist
>
> ALTER TABLE project_task
> ADD CONSTRAINT projecttask_groupprojectid_fk
> FOREIGN KEY (group_project_id)
> REFERENCES project_group_list(group_project_id) ON DELETE CASCADE;
> NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
> check(s)
> ALTER
>
> That command works, but now I think I have 2x as many triggers as I
> want. How do I get rid of the original triggers?
>
>
> alexandria=# \d project_task
> Table "project_task"
> Column | Type | Modifiers
> ------------------+------------------+-----------------------
> project_task_id | integer | not null default
> nextval('project_task_pk_seq'::text)
> group_project_id | integer | not null default '0'
> summary | text | not null default ''
> details | text | not null default ''
> percent_complete | integer | not null default '0'
> priority | integer | not null default '0'
> hours | double precision | not null default '0.00'
> start_date | integer | not null default '0'
> end_date | integer | not null default '0'
> created_by | integer | not null default '0'
> status_id | integer | not null default '0'
> Indexes: projecttask_projid_status
> Primary key: project_task_pkey
> Triggers: RI_ConstraintTrigger_51030049,
> RI_ConstraintTrigger_51030047,
> RI_ConstraintTrigger_4305858,
> RI_ConstraintTrigger_4305852,
> RI_ConstraintTrigger_4305846
>
> After adding the new foreign key:
>
> Triggers: RI_ConstraintTrigger_51364957, ***new
> RI_ConstraintTrigger_51030049,
> RI_ConstraintTrigger_51030047,
> RI_ConstraintTrigger_4305858,
> RI_ConstraintTrigger_4305852,
> RI_ConstraintTrigger_4305846
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ON DELETE CASCADE
@ 2002-12-12 18:17 Dan Langille <dan@langille.org>
parent: Tim Perdue <tim@perdue.net>
2 siblings, 0 replies; 10+ messages in thread
From: Dan Langille @ 2002-12-12 18:17 UTC (permalink / raw)
To: Tim Perdue <tim@perdue.net>; +Cc: pgsql-sql
On 12 Dec 2002 at 11:01, Tim Perdue wrote:
> That command works, but now I think I have 2x as many triggers as I
> want. How do I get rid of the original triggers?
I had to do something similar and documented it at
http://www.freebsddiary.org/postgresql-dropping-constraints.php
hth
--
Dan Langille : http://www.langille.org/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: ON DELETE CASCADE
@ 2002-12-16 02:29 Tim Perdue <tim@perdue.net>
parent: Marie G. Tuite <marie.tuite@edisonaffiliates.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tim Perdue @ 2002-12-16 02:29 UTC (permalink / raw)
To: marie.tuite@edisonaffiliates.com; +Cc: pgsql-sql
Marie G. Tuite wrote:
> Here is a function that I use to list fk(triggers) on a table. Execute the
> function to get the trigger name and then - drop trigger "trigger_name" on
> table_name;
Thanks for the function. Strangely enough, it appears the "extra"
triggers are gone all by themselves. It seems postgres appropriately
deletes or updates the old triggers when you alter the table the second
time, even if you did not drop the prior triggers.
>>I'm trying to comb through my database and add ON DELETE CASCADE to a
>>number of tables where I already have fkeys in place, but I'm having a
>>hard time.
>>
>>ALTER TABLE project_task DROP CONSTRAINT
>>"project_task_group_project_id_f" RESTRICT;
>>
>>ERROR: ALTER TABLE / DROP CONSTRAINT: project_task_group_project_id_f
>>does not exist
>>
>>ALTER TABLE project_task
>> ADD CONSTRAINT projecttask_groupprojectid_fk
>> FOREIGN KEY (group_project_id)
>> REFERENCES project_group_list(group_project_id) ON DELETE CASCADE;
>>NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
>>check(s)
>>ALTER
>>
>>That command works, but now I think I have 2x as many triggers as I
>>want. How do I get rid of the original triggers?
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2002-12-16 02:29 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-14 20:31 On delete cascade! Paulo J. Matos <pocm@rnl.ist.utl.pt>
2002-01-14 23:53 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-01-15 06:29 ` Roberto Mello <rmello@cc.usu.edu>
2002-01-15 11:32 ` Steve Boyle (Roselink) <boylesa@roselink.co.uk>
2002-02-26 07:53 ` Erle Czar Mantos <erle@hindang.msuiit.edu.ph>
2002-12-12 17:01 ON DELETE CASCADE Tim Perdue <tim@perdue.net>
2002-12-12 17:11 ` Re: ON DELETE CASCADE Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-12-12 17:26 ` Re: ON DELETE CASCADE Marie G. Tuite <marie.tuite@edisonaffiliates.com>
2002-12-16 02:29 ` Re: ON DELETE CASCADE Tim Perdue <tim@perdue.net>
2002-12-12 18:17 ` Re: ON DELETE CASCADE Dan Langille <dan@langille.org>
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