MySQL: How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?

Question

I have a table whose primary key is referenced in several other tables as a foreign key. For example:

  CREATE TABLE `X` (
    `X_id` int NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY  (`X_id`)
  )
  CREATE TABLE `Y` (
    `Y_id` int(11) NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    `X_id` int DEFAULT NULL,
    PRIMARY KEY  (`Y_id`),
    CONSTRAINT `Y_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
  )
  CREATE TABLE `Z` (
    `Z_id` int(11) NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    `X_id` int DEFAULT NULL,
    PRIMARY KEY  (`Z_id`),
    CONSTRAINT `Z_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
  )

Now, I dont know how many tables there are in the database that contain foreign keys into X like tables Y and Z. Is there a SQL query that I can use to return: 1. A list of tables that have foreign keys into X AND 2. which of those tables actually have values in the foreign key

Thanks!

Answer 1

You can find all schema related information in the wisely named information_schema table.

You might want to check the table REFERENTIAL_CONSTRAINTS and KEY_COLUMN_USAGE. The former tells you which tables are referenced by others; the latter will tell you how their fields are related.

Answer 2

I can't imagine that finding such references is a solution to your actual problem unless you are writing a database analysis tool and cannot know anything about the data being stored. If that is the case, follow Seb's advice. Otherwise I would recommend finding those relations and adding them to your code. You will do yourself and others looking at your code a great favor.

Answer 3

Can anyone provide the sql query for the question posted above?

I need the same thing as nebs. I need it because I'm trying to create an application which needs to access different databases depending on the user's selection. So yes, it might be similar to a database analysis tool.

Answer 4

Here you go:

USE information_schema;
SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'X' AND REFERENCED_COLUMN_NAME = 'X_id';

If you have multiple databases with similar tables/column names you may also wish to limit your query to a particular database:

SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'X' AND REFERENCED_COLUMN_NAME = 'X_id' AND TABLE_SCHEMA='your_database_name';

Answer 5

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

SELECT ke.referenced_table_name parent, ke.table_name child, ke.constraint_name FROM information_schema.KEY_COLUMN_USAGE ke WHERE ke.referenced_table_name IS NOT NULL ORDER BY ke.referenced_table_name;