I recommend that you declare the tables using ON DELETE CASCADE, so that:
CREATE TABLE rooms (
room_no INT PRIMARY KEY AUTO_INCREMENT,
room_name VARCHAR(255) NOT NULL,
building_no INT NOT NULL,
FOREIGN KEY (building_no)
REFERENCES buildings (building_no)
ON DELETE CASCADE
);
In your case you would have to put ON DELETE CASCADE in the declaration of your detail table, so when you delete it from the main table, the associated record (s) will automatically be deleted.
I leave a link where the theory is explained in case you need it:
link
This way you will not get the error that you need to delete it before. However, if you want to save those records, I would create a table like detail, but with another name, historical_detail, in this way, before deleting the associated record, copy the data and save it in the detail_historical table, so you can Remove from the main table and keep those records even if they do not belong to the same detail table. This can be done automatically with a trigger of the detail table that is executed every time you are going to delete a record of it. Here is an example of a trigger:
How to create a trigger that when deleting a row from a table, delete a related row from another table in mysql?
In that case delete from another table, you are interested in adding in another the record that you are going to delete.
What you say about doing the alter table to remove the restriction of the foreign key, I do not recommend it to you. You would have to do it every time you want to delete a record and if you then go back to add the restriction (I'm not sure if will leave this last so freely if you already have data in the tables) you will have an inconsistent DB because it will have records in detail that will not have in the parent table and this may cause errors in the future ..
