Removing related strings in a many-to-many relationship

Question

I delete the row in the table that is on the same many-to-many relationship site. I would also like to remove any related lines on the other side of this relationship.

For example, let's say I have the following tables, and I want to delete a row from Cars . I would also like to remove any related lines from Drivers and, of course, any lines that are 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 above tables in a SELECT query. But I do not see how to delete relationship data.

Note. Both sides of the relationship implement cascading deletions. So, for example, deleting a row from Cars will delete any related rows in CarDrivers . But obviously this does not apply to the Drivers table.

Answer 1

I think the best approach would be that you should delete the related table data first. In other words, if you want to remove the car and the corresponding drivers that use this car, you will have to remove the drivers first, and then the car. The join table will delete the correct entries due to ON CASCADE DELETE .

Try the following:

 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 do not need to hardcode 1 there, you can use anything, including a parameter, if you use this piece of code in a stored procedure.

Answer 2

Your request does not make sense

Drivers as entities exist separately from Cars. Cars can be driven by many drivers, drivers can drive many cars. This is why you have so many tables.

Please note that the “drivers can drive many cars” bit. This means that if you delete the line Drivers, you need to delete the other lines in CarDrivers.

If you still want to do this, you need a trigger for CarDrivers. CASCADE from Drivers to CarDrivers will delete other CarDrivers lines for you. Cannot remember default behavior for trigger recursion.

What a mess.

Note: this is almost reasonable if you have uniqueness on one of the columns in a many-many table, then it should be a foreign key between Cars and Drivers (Unique on Car means "no more than one driver per car" means NULLable FK in Cars)

Answer 3

There is no relationship between the Drivers and Cars table. This ratio is done through the CarDrivers table. So the problem still exists.

The only way to automate CASCADE deletion is to remove the FK between the CarDrivers and Drivers tables and add a trigger before or after the deletion to CarDrivers to remove the entry in the drivers where driver_id is the one to delete from the row in CarDrivers.

This is not so clean. If deletion is really required in the entire connection table, then the connection would probably be modeled incorrectly, and a cleaner relationship would have to model the relationship just like this: “There are many car drivers” or FK cars in the “Drivers” table, as noted above, for real relationships with cars and drivers, many-to-many relationships are actually correct, and you never delete a driver just because the car has been added / deleted.

Answer 4

Place cascading deletes in the CarDrivers table.

Answer 5

If you have access to the database and have permissions to modify tables, I simply create foreign keys and specify onupdate and oncascade like this:

 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 advantage of this approach is that you do not need to worry about orphaned recordings. At the moment when you delete one record from the car table, all related records in other tables are automatically deleted and updated. Your SQL queries are also shorter.

Answer 6

In Oracle, you can handle this with triggers, in particular 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; / 

Thus, it is enough to issue

 delete from Cars where CarID = 666 

and deletion will be cascaded to the CarDrivers table by restriction and to the Drivers table by trigger.

The use of compound triggers is necessary to avoid ORA-04091, i.e. changing the error table. Complex triggers are available starting with Oracle11g. Look here