Deleting Related Rows in a Many-to-Many Relationship

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 rel开发者_开发问答ationship.

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.

---

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.

---

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)

---

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.

---

Put cascading deletes on the CarDrivers table.

---

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.

---

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.

---

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.