Skip navigation links
MySQL 5.0 Reference Manual :: 19 INFORMATION_SCHEMA Tables :: 19.13 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
19.14 The INFORMATION_SCHEMA ROUTINES Table »
Section Navigation      [Toggle]

19.13. The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

The KEY_COLUMN_USAGE table describes which key columns have constraints.

INFORMATION_SCHEMA Name SHOW Name Remarks
CONSTRAINT_CATALOG   NULL
CONSTRAINT_SCHEMA    
CONSTRAINT_NAME    
TABLE_CATALOG    
TABLE_SCHEMA    
TABLE_NAME    
COLUMN_NAME    
ORDINAL_POSITION    
POSITION_IN_UNIQUE_CONSTRAINT    
REFERENCED_TABLE_SCHEMA    
REFERENCED_TABLE_NAME    
REFERENCED_COLUMN_NAME    

Notes:

  • If the constraint is a foreign key, then this is the column of the foreign key, not the column that the foreign key references.

  • The value of ORDINAL_POSITION is the column's position within the constraint, not the column's position within the table. Column positions are numbered beginning with 1.

  • The value of POSITION_IN_UNIQUE_CONSTRAINT is NULL for unique and primary-key constraints. For foreign-key constraints, it is the ordinal position in key of the table that is being referenced.

    Suppose that there are two tables name t1 and t3 that have the following definitions: 

    CREATE TABLE t1
(
    s1 INT,
    s2 INT,
    s3 INT,
    PRIMARY KEY(s3)
) ENGINE=InnoDB;

CREATE TABLE t3
(
    s1 INT,
    s2 INT,
    s3 INT,
    KEY(s1),
    CONSTRAINT CO FOREIGN KEY (s2) REFERENCES t1(s3)
) ENGINE=InnoDB;

    For those two tables, the KEY_COLUMN_USAGE table has two rows:

    • One row with CONSTRAINT_NAME = 'PRIMARY', TABLE_NAME = 't1', COLUMN_NAME = 's3', ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = NULL.

    • One row with CONSTRAINT_NAME = 'CO', TABLE_NAME = 't3', COLUMN_NAME = 's2', ORDINAL_POSITION = 1, POSITION_IN_UNIQUE_CONSTRAINT = 1.

  • REFERENCED_TABLE_SCHEMA, REFERENCED_TABLE_NAME, and REFERENCED_COLUMN_NAME were added in MySQL 5.0.6.


User Comments

Posted by James Goatcher on May 27 2008 6:36pm[Delete] [Edit]

A quick way to list your FKs (Foreign Key references) using the KEY_COLUMN_USAGE view:

SELECT CONCAT( table_name, '.',
column_name, ' -> ',
referenced_table_name, '.',
referenced_column_name ) AS list_of_fks
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = (your schema name here)
AND REFERENCED_TABLE_NAME is not null
ORDER BY TABLE_NAME, COLUMN_NAME;

This query does assume that the constraints and all referenced and referencing tables are in the same schema.

Add your own comment.