0

I know, that there is a thing in MySQL called 'on delete cascade' and that means (as far as i can understand): when you delete a certain row in a table, all records in the other tables, that have a reference on it will be deleted too. And that happens without any other queries or delete statements. So now my problem is: I have a situation, that is pretty similar to that one. There is a table (A) with a primary key column, and a certain amount of other tables (B, C, ...) have a column, which references (foreign key) to that column in table A. Now i have a row in A with the primary key 22 and i want to determine all the occurrences of 22 in the other tables. So it is possible, that 22 occurs in C and F, but not in B, D or E ...

If it's possible to delete every reference with 'on delete cascade', can i realize my problem on a similar way?

My solution so far: i look up in every table, if there is a 22 in the foreign key column, but that may take a long time, when there is a large amount of tables/rows in it. Are there any better solutions?

Yan_Yan
  • 67
  • 7
  • On delete cascade should work for what you intend, but you’re miss understanding how “on delete cascade” works. On delete cascade is associated to the **key**. Check this link for a full answer and example: https://stackoverflow.com/questions/2914936/mysql-foreign-key-constraints-cascade-delete – H. Figueiredo Jun 07 '19 at 18:57
  • IMHO there is no "better" way as to query every referencing table. But you could automate the queries by fetching the FKs from the `information_schema`. Another "trick" could be to delete the parent row in a (non commited) transaction and look for FK constraint violations and number of affected rows. But I wouldn't try that in production. – Paul Spiegel Jun 07 '19 at 21:17

0 Answers0