CODEWITHSUNDEEP
mysqlsql
TehEbil
TehEbil

Reputation: 88

MySQL - need to delete a row which has many links to other tables

I need to delete a row which has many connections to other table. Let's say I have two Accounts in my users table which I want to merge. So at first I want to change the id of all rows from Account A's id to Account B's id and after that, I want to delete Account B.

Is there a SQL Command to list every row from every table which point to exactly this one row in the users table? Sometimes the foreign_key is called user_id, sometimes create_user_id, and it even might happen that there is no foreign_key. But the latter isn't important.

This would be very helpful. Thank you for your help!

Kind Regards

Upvotes: 1

Views: 46

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562300

You can query to find tables that reference your Accounts table:

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'Accounts';

Then for each of these, you can UPDATE the references if any exist.

You don't need to know the constraint name or the column name in the dependent table.

In cases where the table does not have a foreign key referencing your Accounts table, you're on your own. You must know your own schema enough to know which tables these are.

Upvotes: 1

Related Questions