Deleting Related Rows in a Many-to-Many Relationship

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.