How to find all tables that have foreign keys that

问题:

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 don\'t 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
2. AND which of those tables actually have values in the foreign key

回答1:

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\';

回答2:

MySQL 5.5 Reference Manual: \"InnoDB and FOREIGN KEY Constraints\"

SELECT
  ke.REFERENCED_TABLE_SCHEMA parentSchema,
  ke.referenced_table_name parentTable,
  ke.REFERENCED_COLUMN_NAME parentColumnName,
  ke.TABLE_SCHEMA ChildSchema,
  ke.table_name childTable,
  ke.COLUMN_NAME ChildColumnName
FROM
  information_schema.KEY_COLUMN_USAGE ke
WHERE
  ke.referenced_table_name IS NOT NULL
  AND ke.REFERENCED_COLUMN_NAME = \'ci_id\' ## Find Foreign Keys linked to this Primary Key
ORDER BY
  ke.referenced_table_name;

回答3:

This solution will not only display all relations but also the constraint name, which is required in some cases (e.g. drop constraint):

SELECT
    CONCAT(table_name, \'.\', column_name) AS \'foreign key\',
    CONCAT(referenced_table_name, \'.\', referenced_column_name) AS \'references\',
    constraint_name AS \'constraint name\'
FROM
    information_schema.key_column_usage
WHERE
    referenced_table_name IS NOT NULL;

If you want to check tables in a specific database, add the following:

AND table_schema = \'database_name\';

回答4:

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.

回答5:

I wrote a little bash onliner that you can write to a script to get a friendly output:

mysql_references_to:

mysql -uUSER -pPASS -A DB_NAME -se \"USE information_schema; SELECT * FROM KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = \'$1\' AND REFERENCED_COLUMN_NAME = \'id\'\\G\" | sed \'s/^[ \\t]*//;s/[ \\t]*$//\' |egrep \"\\<TABLE_NAME|\\<COLUMN_NAME\" |sed \'s/TABLE_NAME: /./g\' |sed \'s/COLUMN_NAME: //g\' | paste -sd \",\" -| tr \'.\' \'\\n\' |sed \'s/,$//\' |sed \'s/,/./\'

So the execution: mysql_references_to transaccion (where transaccion is a random table name) gives an output like this:

carrito_transaccion.transaccion_id
comanda_detalle.transaccion_id
comanda_detalle_devolucion.transaccion_positiva_id
comanda_detalle_devolucion.transaccion_negativa_id
comanda_transaccion.transaccion_id
cuenta_operacion.transaccion_id
...

回答6:

Listing all foreign keys in a db including description

    SELECT  
    i1.CONSTRAINT_NAME, i1.TABLE_NAME,i1.COLUMN_NAME,
    i1.REFERENCED_TABLE_SCHEMA,i1.REFERENCED_TABLE_NAME, i1.REFERENCED_COLUMN_NAME,
    i2.UPDATE_RULE, i2.DELETE_RULE 
    FROM   
    information_schema.KEY_COLUMN_USAGE AS i1  
    INNER JOIN 
    information_schema.REFERENTIAL_CONSTRAINTS AS i2 
    ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
    WHERE i1.REFERENCED_TABLE_NAME IS NOT NULL  
    AND  i1.TABLE_SCHEMA  =\'db_name\';

restricting to a specific column in a table table

AND i1.table_name = \'target_tb_name\' AND i1.column_name = \'target_col_name\'

回答7:

Easiest:
1. Open phpMyAdmin
2. On the left click database name
3. On the top right corner find \"Designer\" tab

All constraints will be shown there.