Обсуждение: Delete with foreign keys
I have three tables -- CREATE TABLE name (id INT PRIMARY KEY, str VARCHAR(20)); CREATE TABLE place (id INT PRIMARY KEY, name_id INT REFERENCESname(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 nameWHERE 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
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 nameWHERE 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
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