CODEWITHSUNDEEP
mysqlsql
user2242044
user2242044

Reputation: 9213

Getting records from other tables that have a particular foreign key

I have a MySQL table called User where userid is the primary key. When someone tries to delete a user, I need to see if that user is referenced on other tables. I'd prefer not to make a dozen different queries and wondering if there is some information_schema level check.

I know you can find which tables reference a column, but is there a way to add which record are reference?

To get which tables reference this table/column

SELECT *
FROM
  information_schema.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_NAME = 'User'
  AND REFERENCED_COLUMN_NAME = 'userid';

Upvotes: 0

Views: 49

Answers (1)

Nate Reynolds
Nate Reynolds

Reputation: 137

By you question it appears to me this may be a vendor database. I've spent many, many days scouring databases hunting for foreign references in vendor databases where I work. Unfortunately, there's not a dynamic way I've found to hunt down all foreign references for each insert/update/delete, and if you could mad-wizard something to automagically do this, it would most likely be way more expensive than multiple statements to cover each table.

If the database's design isn't insane, it's likely the foreign columns all use the same name of userid (this is a BIG if). You could search through the information_schema looking for all columns with the name userid. This would provide a list of tables to examine more closely for foreign references. To cast an even wider net, instead of putting NAME='userid' in the WHERE clause use NAME LIKE '%userid%'.

Another strategy I've used in these instances is a database compare tool. Being a SQL Server guy I've only used Visual Studio's built-in tool so I don't have any specific recommendations for MySQL. Anyway, unless your production database has very little activity, take two backups of the database. Restore both to your server to different names (like 'test_vanilla' and 'test_change'). Point your application to the 'test_change' database, delete a user, run the compare tool against both databases and see what's changed. This will show you what tables got hit in the delete operation. This is probably the most tedious, but also will give you the most definitive and accurate results.

Hopefully that points you in the right direction. Good luck!

Upvotes: 1

Related Questions