12

I'm deleting a row in a table that is on one site of a many-to-many relationship. I would also like to delete any related rows on the other side of that relationship.

For example, let's say I have the following tables and I want to delete a row from Cars. I would also want to delete any related rows from Drivers and, of course, any rows no longer needed in CarDrivers.

Table Cars:
CarID      int
CarName    nvarchar(100)

Table Drivers:
DriverID   int
DriverName nvarchar(100)

Table CarDrivers:
CarID      int
Driver     int

I know how to join the tables above in a SELECT query. But I don't see how to delete data across the relationship.

Note: Both sides of the relationship implement cascading deletes. So, for example, deleting a row from Cars will delete any related rows in CarDrivers. But obviously that doesn't propagate to the Drivers table.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466
  • possible duplicate of [How do I delete from multiple tables using INNER JOIN in SQL server](http://stackoverflow.com/questions/783726/how-do-i-delete-from-multiple-tables-using-inner-join-in-sql-server) – OMG Ponies Oct 14 '11 at 03:07
  • 1
    @OMG Ponies: disgree, it isn't a chained delete because the CarDrivers to Drivers will be a child deleting a parent. The parent may have other children. See my answer please – gbn Oct 14 '11 at 03:11
  • 1
    -1 for misleading and incomplete questions with a bad example – gbn Oct 14 '11 at 03:50

7 Answers7

7

I think the best approach would be that you would have to delete the related table's data first. In other words, if you wanted to delete a Car and the corresponding Drivers that utilize that car, you'd have to delete the Drivers first, and then the Car. The join table will delete the correct records because of ON CASCADE DELETE.

Try this:

delete
from Drivers
where DriverID in
(
    select d.DriverID
    from Drivers d
    inner join CarDrivers cd
    on d.DriverID = cd.Driver
    inner join Cars c
    on c.CarID = cd.CarID
    where c.CarID = 1
)

delete
from Cars
where CarID = 1

Naturally, you don't need to hardcode the 1 there, you could use anything including a parameter if you are utilizing this code snippet in a stored proc.

3

Your request doesn't make sense

Drivers as entities exists separately from Cars. Cars can be driven by many drivers, drivers can drive many cars. This is why you have the many-many table.

Note the "drivers can drive many cars" bit. This means if you delete the Drivers row, you need to delete other rows in CarDrivers.

If you still want to do this, you need a trigger on CarDrivers. The CASCADE from Drivers to CarDrivers will delete other CarDrivers rows for you. Can't remember the default behaviour for trigger recursion too.

What a mess.

Note: this almost makes sense if you have uniqueness on one of the columns in the many-many table then it should be a foreign key between Cars and Drivers (Unique on Car means "at most one driver per car" means NULLable FK column in Cars)

gbn
  • 422,506
  • 82
  • 585
  • 676
  • The Car, Drivers, and CarDrivers tables were just an example to illustrate what I'm trying to do. This is what an app I'm working with does. I'll review the design in the ways that you described, but it may not be up to me to change the design. I think it has something to do with the fact that one of the tables is linked to other tables and the junction table contains a couple of additional fields. – Jonathan Wood Oct 14 '11 at 03:13
  • @Jonathan Wood: the design is sound if you need a many-many table. It is the request that is un-sound – gbn Oct 14 '11 at 03:16
  • Looking at it further, it seems that it's a one-to-many relationship; however, a junction table is used primarily for the reasons I outlined. I can assure you that it makes no sense to leave orphaned items on the other side of the relationship. – Jonathan Wood Oct 14 '11 at 03:41
  • @Jonathan Wood: Expand your question with all tables then so we can critique the design properly. You have given a bad example that doesn't reflect reality, you'll get bad answers that won't solve your problem correctly. – gbn Oct 14 '11 at 03:50
  • @Jonathan Wood: I assume you didn't understand my answer then: deleting *across* a many-many is cascading Cars -> CarDrivers -> Drivers -> carDrivers -> Drivers -> CarDrivers etc. Feel free to accept Shark's or wait for another one. – gbn Oct 14 '11 at 14:54
1

There is no relationship between the Drivers and the Cars table. This relationship is via the CarDrivers table. Thus, the problem still exists.

The only way I know to automate the CASCADE delete is to remove the FK between CarDrivers and Drivers table and add a before or after delete trigger to CarDrivers to delete the entry in drivers where the driver_id is the one of the row being deleted in CarDrivers.

This is not clean in so many ways. If the delete is actually required across the join table, then the relationship is probably modeled wrong and a cleaner relationship would have been to have modeled the relationship simply as 'there are many drivers of a car' or a FK of Cars in the Drivers table. As noted above, for the actual cars and drivers relationship a many-to-many relationship is actually correct and you would never delete a driver just because the car was totalled/deleted.

dcaswell
  • 3,137
  • 2
  • 26
  • 25
Gary
  • 11
  • 1
0

In Oracle you can handle it using triggers, specifically compound triggers

alter table CarDrivers add CONSTRAINT CarFK FOREIGN KEY (CarID)
      REFERENCES Cars (CarID) on delete cascade enable
/    

create or replace TRIGGER "CarDrivers_delete"  
for delete ON CarDrivers 
compound trigger
  type driver_ids is table of Drivers.DriverID%type INDEX BY PLS_INTEGER;
  ids driver_ids;

  AFTER EACH ROW IS    
  BEGIN   
      ids (ids.COUNT + 1) := :NEW.Driver;    
  END AFTER EACH ROW;

   AFTER STATEMENT IS    
   BEGIN      
      FOR i IN 1 .. ids.COUNT    
      LOOP                                      
            delete from Drivers
             WHERE DriverID = ids (i);    
      END LOOP;    
   END AFTER STATEMENT;      
END;
/

This way it's enough to issue

delete from Cars where CarID = 666

and the deletion would be cascade to the CarDrivers table by the constraint and to the Drivers table by the trigger.

The use of compound triggers is necessary to avoid ORA-04091, that is mutating table errors. Compound triggers are available since Oracle11g. See here.

user1593165
  • 503
  • 3
  • 6
0

I had a similar issue with my project (using node.js with knex and postgres).

My many-to-many table foreign keys however were both not-nullable, which may be different from your example as presumably a car can exist in the database without a driver.

With the not-nullable, restrict and cascade applied to the many-to-many, I found deleting from each table individually, in reverse order from which they were migrated, worked well. With node/knex, I imported these functions to other files where they were needed to be used as callbacks, and avoid repetition.

It's probably not the optimal way, (sub-queries in knex don't read great) but suffices to get things working at a baseline.

NWKendall
  • 1
  • 1
0

Put cascading deletes on the CarDrivers table.

toop
  • 10,834
  • 24
  • 66
  • 87
  • That will only delete the related data in the CarDrivers table. I believe the OP wants to also delete the corresponding table data that is part of the join. In which case, cascading deletes won't fulfill the need. –  Oct 14 '11 at 03:09
0

If you have access to database and have permissions to alter the tables, I would just create foreign keys and specify onupdate and oncascade as so:

ALTER TABLE [dbo].[Drivers]  WITH CHECK ADD  CONSTRAINT [FK__Cars] FOREIGN KEY([CarID])
REFERENCES [dbo].[Car] ([CarID])
ON UPDATE CASCADE
ON DELETE CASCADE

ALTER TABLE [dbo].[CarDrivers]  WITH CHECK ADD  CONSTRAINT [FK_Drivers_Cars] FOREIGN KEY([CarID])
REFERENCES [dbo].[Car] ([CarID])
ON UPDATE CASCADE
ON DELETE CASCADE

The benefit of this approach is that you don't need to worry about orphan records. The moment you delete one record from the Car table, all related in the other tables are automatically deleted and updated. Your SQL statements are shorter, too.

Icarus
  • 63,293
  • 14
  • 100
  • 115