I have a (very) complicated db structure. One of the tables represents a customer. This table is referenced by other child- tables, and these child tables are referenced too (not necessarily tree structure).
I have to delete a parent record in the customers table, without changing constraints definitions(I do not want to use delete on cascade!!!).
I thought of to implement this recursively in mySQL, but that seems to be super complicated (in each step I have to figure if I visited the table, if its a leaf table, and save the key of the parent table so I can delete the records of the child table associated with it).
I want to use recursion and not to use the iterative way that relays on the data tables because when someone in the future enters a new table I will have to change the algorithm.. and I want it to stay consistent.
eample: if that is the hirarchy (tab represents a child)
customers
code_831
site_sales_billings
cus_dev_devid
custom_wizard_containers
unlock_wizard_request
customer_attendant_console
customer_attendant_console_row
customer_attendant_consoles
customer_region_attendant_consoles
customer_region_attendant_console_rows
I want first to delete rows from the leaves tables: site_sales_billings , cus_dev_devid, unlock_wizard_request, customer_attendant_console_row, customer_region_attendant_console_rows and than upwords until I will have permission to delete a customer record without constraint error..