pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDelete with foreign keys
3+ messages / 2 participants
[nested] [flat]
* Delete with foreign keys
@ 2008-02-22 18:00 felix@crowfix.com
2008-02-23 07:12 ` Re: Delete with foreign keys dipesh <dipesh.mistry@mobilefundas.com>
0 siblings, 1 reply; 3+ messages in thread
From: felix@crowfix.com @ 2008-02-22 18:00 UTC (permalink / raw)
To: pgsql-sql
I have three tables --
CREATE TABLE name (id INT PRIMARY KEY, str VARCHAR(20));
CREATE TABLE place (id INT PRIMARY KEY, name_id INT REFERENCES name(id));
CREATE TABLE data (id INT PRIMARY KEY, place_id INT REFERENCES place(id));
I want to delete all place and data rows which reference specific
names, but not the names themselves. I can do it like this:
DELETE FROM data d WHERE exists (SELECT id from place p WHERE d.place_id = p.id AND p.name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%'));
DELETE FROM place WHERE name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%');
but it seems rather roundabout, and I wonder whether the EXISTS and IN
business is slow. Is there some way to do it using JOINs? I think of
something like this:
DELETE FROM place p, name n WHERE p.name_id = n.id AND n.str LIKE 'Fredonia%';
but I don't want to delete the name rows. Then I think of this:
DELETE FROM place p WHERE p.name_id = name.id AND name.str LIKE 'Fredonia%';
but I feel uneasy about the two separate name references when the
table is not named in the FROM clause. Maybe that's just my novicity.
I also wonder about getting fancy and ending up with SQL specific to a
database; I don't have any plans to migrate, but I try to avoid
branding my SQL.
--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Delete with foreign keys
2008-02-22 18:00 Delete with foreign keys felix@crowfix.com
@ 2008-02-23 07:12 ` dipesh <dipesh.mistry@mobilefundas.com>
2008-02-23 07:47 ` Re: Delete with foreign keys felix@crowfix.com
0 siblings, 1 reply; 3+ messages in thread
From: dipesh @ 2008-02-23 07:12 UTC (permalink / raw)
To: felix@crowfix.com; +Cc: pgsql-sql
felix@crowfix.com wrote:
> I have three tables --
>
> CREATE TABLE name (id INT PRIMARY KEY, str VARCHAR(20));
> CREATE TABLE place (id INT PRIMARY KEY, name_id INT REFERENCES name(id));
> CREATE TABLE data (id INT PRIMARY KEY, place_id INT REFERENCES place(id));
>
> I want to delete all place and data rows which reference specific
> names, but not the names themselves. I can do it like this:
>
> DELETE FROM data d WHERE exists (SELECT id from place p WHERE d.place_id = p.id AND p.name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%'));
>
> DELETE FROM place WHERE name_id IN (SELECT id FROM name WHERE str LIKE 'Fredonia%');
>
> but it seems rather roundabout, and I wonder whether the EXISTS and IN
> business is slow. Is there some way to do it using JOINs? I think of
> something like this:
>
> DELETE FROM place p, name n WHERE p.name_id = n.id AND n.str LIKE 'Fredonia%';
>
> but I don't want to delete the name rows. Then I think of this:
>
> DELETE FROM place p WHERE p.name_id = name.id AND name.str LIKE 'Fredonia%';
>
> but I feel uneasy about the two separate name references when the
> table is not named in the FROM clause. Maybe that's just my novicity.
>
> I also wonder about getting fancy and ending up with SQL specific to a
> database; I don't have any plans to migrate, but I try to avoid
> branding my SQL.
>
>
Hello, I am Dipesh Mistry from Ahmedabad-India.
I have the solution for you.
First drop the constraint on table data.
Example.
alter table data drop constraint data_place_id_fkey;
And in second step add new Constraint.
Example.
alter table data add constraint data_place_id_fkey FOREIGN
KEY(place_id) REFERENCES place(id) ON DELETE CASCADE ON UPDATE CASCADE;
Now when you test query like,
delete from place where name_id in(select id from name where str='dip%');
So, you can try this above step.
--
With Warm Regards,
Dipesh Mistry
Information Technology Dept.
GaneshaSpeaks.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Delete with foreign keys
2008-02-22 18:00 Delete with foreign keys felix@crowfix.com
2008-02-23 07:12 ` Re: Delete with foreign keys dipesh <dipesh.mistry@mobilefundas.com>
@ 2008-02-23 07:47 ` felix@crowfix.com
0 siblings, 0 replies; 3+ messages in thread
From: felix@crowfix.com @ 2008-02-23 07:47 UTC (permalink / raw)
To: pgsql-sql
On Sat, Feb 23, 2008 at 12:42:13PM +0530, dipesh wrote:
> alter table data add constraint data_place_id_fkey FOREIGN KEY(place_id)
> REFERENCES place(id) ON DELETE CASCADE ON UPDATE CASCADE;
Interestingly different way of doing it. Thanks. It makes me think
too :-)
--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix@crowfix.com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-02-23 07:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-02-22 18:00 Delete with foreign keys felix@crowfix.com
2008-02-23 07:12 ` dipesh <dipesh.mistry@mobilefundas.com>
2008-02-23 07:47 ` felix@crowfix.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