mysql - How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?


Translate

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

所有的回答
  • Translate

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

  • Translate

    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;
    

  • Translate

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

  • Translate

    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.


  • Translate

    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'
    

  • Translate

    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
    ...
    

  • Translate

    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.