pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCan i force deletion of dependent rows?
7+ messages / 5 participants
[nested] [flat]
* Can i force deletion of dependent rows?
@ 2010-02-13 06:11 Shruthi A <shruthi.iisc@gmail.com>
2010-02-13 07:05 ` Re: Can i force deletion of dependent rows? Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 1 reply; 7+ messages in thread
From: Shruthi A @ 2010-02-13 06:11 UTC (permalink / raw)
To: pgsql-sql
Hello,
I have 2 tables (A and B) where the table B has a foreign key reference to
table A. Like this:
create table A (x int primary key);
create table B (y int primary key, z int references A (x) );
As you can see, i've not specified any further 'on delete' clause to the
foreign key constraint like 'on delete cascade' or 'on delete restrict' or
'on delete set null' or 'on delete set default'. This means that the
default behavior is followed ie 'on delete restrict' holds by default.
Now I want to delete a particular row from table A. Is there any way I can
force deletion of all the dependent rows in table B?
Note that the tables have already been created in the above manner and
populated. I want to do this deletion through DML only ie without any
change in the table definition.
Thanks,
Shruthi
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can i force deletion of dependent rows?
2010-02-13 06:11 Can i force deletion of dependent rows? Shruthi A <shruthi.iisc@gmail.com>
@ 2010-02-13 07:05 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-02-13 11:23 ` Re: Can i force deletion of dependent rows? Wayne E. Seguin <wayneeseguin@gmail.com>
2010-02-15 19:04 ` Re: Can i force deletion of dependent rows? Rob Sargent <robjsargent@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Tim Landscheidt @ 2010-02-13 07:05 UTC (permalink / raw)
To: pgsql-sql
Shruthi A <shruthi.iisc@gmail.com> wrote:
> I have 2 tables (A and B) where the table B has a foreign key reference to
> table A. Like this:
> create table A (x int primary key);
> create table B (y int primary key, z int references A (x) );
> As you can see, i've not specified any further 'on delete' clause to the
> foreign key constraint like 'on delete cascade' or 'on delete restrict' or
> 'on delete set null' or 'on delete set default'. This means that the
> default behavior is followed ie 'on delete restrict' holds by default.
> Now I want to delete a particular row from table A. Is there any way I can
> force deletion of all the dependent rows in table B?
> Note that the tables have already been created in the above manner and
> populated. I want to do this deletion through DML only ie without any
> change in the table definition.
"DELETE FROM B WHERE z = 'your x';"?
Tim
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can i force deletion of dependent rows?
2010-02-13 06:11 Can i force deletion of dependent rows? Shruthi A <shruthi.iisc@gmail.com>
2010-02-13 07:05 ` Re: Can i force deletion of dependent rows? Tim Landscheidt <tim@tim-landscheidt.de>
@ 2010-02-13 11:23 ` Wayne E. Seguin <wayneeseguin@gmail.com>
2010-02-13 11:32 ` Re: Can i force deletion of dependent rows? Shoaib Mir <shoaibmir@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Wayne E. Seguin @ 2010-02-13 11:23 UTC (permalink / raw)
To: pgsql-sql
Is there another answer, asside from in the application and/or as a
trigger using `DELETE FROM B WHERE z = 'x';` as it's body?
~Wayne
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can i force deletion of dependent rows?
2010-02-13 06:11 Can i force deletion of dependent rows? Shruthi A <shruthi.iisc@gmail.com>
2010-02-13 07:05 ` Re: Can i force deletion of dependent rows? Tim Landscheidt <tim@tim-landscheidt.de>
2010-02-13 11:23 ` Re: Can i force deletion of dependent rows? Wayne E. Seguin <wayneeseguin@gmail.com>
@ 2010-02-13 11:32 ` Shoaib Mir <shoaibmir@gmail.com>
2010-02-13 17:25 ` Re: Can i force deletion of dependent rows? Shruthi A <shruthi.iisc@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Shoaib Mir @ 2010-02-13 11:32 UTC (permalink / raw)
To: Wayne E. Seguin <wayneeseguin@gmail.com>; +Cc: pgsql-sql
On Sat, Feb 13, 2010 at 10:23 PM, Wayne E. Seguin <wayneeseguin@gmail.com>wrote:
> Is there another answer, asside from in the application and/or as a
> trigger using `DELETE FROM B WHERE z = 'x';` as it's body?
>
>
>
Did you look at ON DELETE CASCADE option, check out
http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html
CREATE TABLE order_items (
product_no integer REFERENCES products *ON DELETE RESTRICT*,
order_id integer REFERENCES orders *ON DELETE CASCADE*,
quantity integer,
PRIMARY KEY (product_no, order_id)
);
--
Shoaib Mir
http://shoaibmir.wordpress.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can i force deletion of dependent rows?
2010-02-13 06:11 Can i force deletion of dependent rows? Shruthi A <shruthi.iisc@gmail.com>
2010-02-13 07:05 ` Re: Can i force deletion of dependent rows? Tim Landscheidt <tim@tim-landscheidt.de>
2010-02-13 11:23 ` Re: Can i force deletion of dependent rows? Wayne E. Seguin <wayneeseguin@gmail.com>
2010-02-13 11:32 ` Re: Can i force deletion of dependent rows? Shoaib Mir <shoaibmir@gmail.com>
@ 2010-02-13 17:25 ` Shruthi A <shruthi.iisc@gmail.com>
2010-02-13 22:45 ` Re: Can i force deletion of dependent rows? Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 1 reply; 7+ messages in thread
From: Shruthi A @ 2010-02-13 17:25 UTC (permalink / raw)
To: Shoaib Mir <shoaibmir@gmail.com>; Wayne E. Seguin <wayneeseguin@gmail.com>; pgsql-sql
Thanks people, but the trigger doesnt really solve my problem. You see,
there is actually a CHAIN of dependencies where B references A, C references
B, D and E reference C and so on... So if i start writing triggers for all
these, there'll be a huge process to follow. I'm not strictly against it, i
might use it as a last resort, but i'm asking if a simple DML statement
would solve my problem.
Dear Shoaib, I did mention in a note that I don't want to recreate the 2
tables with the 'on delete cascade' clause. The tables are ALREADY created
and populated. And they are huge. So i cannot afford to drop and recreate
them. I want an option in DML only. If that is possible that is.
Thanks,
Shruthi
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can i force deletion of dependent rows?
2010-02-13 06:11 Can i force deletion of dependent rows? Shruthi A <shruthi.iisc@gmail.com>
2010-02-13 07:05 ` Re: Can i force deletion of dependent rows? Tim Landscheidt <tim@tim-landscheidt.de>
2010-02-13 11:23 ` Re: Can i force deletion of dependent rows? Wayne E. Seguin <wayneeseguin@gmail.com>
2010-02-13 11:32 ` Re: Can i force deletion of dependent rows? Shoaib Mir <shoaibmir@gmail.com>
2010-02-13 17:25 ` Re: Can i force deletion of dependent rows? Shruthi A <shruthi.iisc@gmail.com>
@ 2010-02-13 22:45 ` Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 0 replies; 7+ messages in thread
From: Tim Landscheidt @ 2010-02-13 22:45 UTC (permalink / raw)
To: pgsql-sql
Shruthi A <shruthi.iisc@gmail.com> wrote:
> Thanks people, but the trigger doesnt really solve my problem. You see,
> there is actually a CHAIN of dependencies where B references A, C references
> B, D and E reference C and so on... So if i start writing triggers for all
> these, there'll be a huge process to follow. I'm not strictly against it, i
> might use it as a last resort, but i'm asking if a simple DML statement
> would solve my problem.
> Dear Shoaib, I did mention in a note that I don't want to recreate the 2
> tables with the 'on delete cascade' clause. The tables are ALREADY created
> and populated. And they are huge. So i cannot afford to drop and recreate
> them. I want an option in DML only. If that is possible that is.
You don't have to drop and recreate them, you could just
temporarily change the foreign key definition (untested):
| BEGIN WORK;
| ALTER TABLE B DROP CONSTRAINT B_z_FKey;
| ALTER TABLE B ADD CONSTRAINT B_z_FKey FOREIGN KEY(z) REFERENCES A(x) ON DELETE CASCADE;
| DELETE FROM A WHERE x = 'your x';
| ALTER TABLE B DROP CONSTRAINT B_z_FKey;
| ALTER TABLE B ADD CONSTRAINT B_z_FKey FOREIGN KEY(z) REFERENCES A(x) ON DELETE NO ACTION;
| COMMIT WORK;
But obviously this is no better than a simple "DELETE FROM B
WHERE z = 'your x';" as you still have to name the dependen-
cy chain yourself.
You can query the information_schema to build this chain
(e. g. cf.
<URI:http://sqlserver2000.databases.aspfaq.com/schema-how-do-i-find-all-the-foreign-keys-in-a-database.ht...;),
but I would invest more time in rethinking your processes.
Tim
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Can i force deletion of dependent rows?
2010-02-13 06:11 Can i force deletion of dependent rows? Shruthi A <shruthi.iisc@gmail.com>
2010-02-13 07:05 ` Re: Can i force deletion of dependent rows? Tim Landscheidt <tim@tim-landscheidt.de>
@ 2010-02-15 19:04 ` Rob Sargent <robjsargent@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rob Sargent @ 2010-02-15 19:04 UTC (permalink / raw)
To: pgsql-sql
then I think OP needs to delete A where "your x";
On 02/13/2010 12:05 AM, Tim Landscheidt wrote:
> Shruthi A<shruthi.iisc@gmail.com> wrote:
>
>> I have 2 tables (A and B) where the table B has a foreign key reference to
>> table A. Like this:
>
>> create table A (x int primary key);
>> create table B (y int primary key, z int references A (x) );
>
>> As you can see, i've not specified any further 'on delete' clause to the
>> foreign key constraint like 'on delete cascade' or 'on delete restrict' or
>> 'on delete set null' or 'on delete set default'. This means that the
>> default behavior is followed ie 'on delete restrict' holds by default.
>
>> Now I want to delete a particular row from table A. Is there any way I can
>> force deletion of all the dependent rows in table B?
>
>> Note that the tables have already been created in the above manner and
>> populated. I want to do this deletion through DML only ie without any
>> change in the table definition.
>
> "DELETE FROM B WHERE z = 'your x';"?
>
> Tim
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2010-02-15 19:04 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-13 06:11 Can i force deletion of dependent rows? Shruthi A <shruthi.iisc@gmail.com>
2010-02-13 07:05 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-02-13 11:23 ` Wayne E. Seguin <wayneeseguin@gmail.com>
2010-02-13 11:32 ` Shoaib Mir <shoaibmir@gmail.com>
2010-02-13 17:25 ` Shruthi A <shruthi.iisc@gmail.com>
2010-02-13 22:45 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-02-15 19:04 ` Rob Sargent <robjsargent@gmail.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