- 19 INFORMATION_SCHEMA Tables
- 19.1 The INFORMATION_SCHEMA SCHEMATA Table
- 19.2 The INFORMATION_SCHEMA TABLES Table
- 19.3 The INFORMATION_SCHEMA COLUMNS Table
- 19.4 The INFORMATION_SCHEMA STATISTICS Table
- 19.5 The INFORMATION_SCHEMA USER_PRIVILEGES Table
- 19.6 The INFORMATION_SCHEMA SCHEMA_PRIVILEGES Table
- 19.7 The INFORMATION_SCHEMA TABLE_PRIVILEGES Table
- 19.8 The INFORMATION_SCHEMA COLUMN_PRIVILEGES Table
- 19.9 The INFORMATION_SCHEMA CHARACTER_SETS Table
- 19.10 The INFORMATION_SCHEMA COLLATIONS Table
- 19.11 The INFORMATION_SCHEMA COLLATION_CHARACTER_SET_APPLICABILITY Table
- 19.12 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table
- 19.13 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table
- 19.14 The INFORMATION_SCHEMA ROUTINES Table
- 19.15 The INFORMATION_SCHEMA VIEWS Table
- 19.16 The INFORMATION_SCHEMA TRIGGERS Table
- 19.17 The INFORMATION_SCHEMA PLUGINS Table
- 19.18 The INFORMATION_SCHEMA ENGINES Table
- 19.19 The INFORMATION_SCHEMA PARTITIONS Table
- 19.20 The INFORMATION_SCHEMA EVENTS Table
- 19.21 The INFORMATION_SCHEMA FILES Table
- 19.22 The INFORMATION_SCHEMA TABLESPACES Table
- 19.23 The INFORMATION_SCHEMA PROCESSLIST Table
- 19.24 The INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS Table
- 19.25 The INFORMATION_SCHEMA GLOBAL_STATUS and SESSION_STATUS Tables
- 19.26 The INFORMATION_SCHEMA GLOBAL_VARIABLES and SESSION_VARIABLES Tables
- 19.27 The INFORMATION_SCHEMA PARAMETERS Table
- 19.28 The INFORMATION_SCHEMA PROFILING Table
- 19.29 Other INFORMATION_SCHEMA Tables
- 19.30 Extensions to SHOW Statements
The KEY_COLUMN_USAGE table describes
which key columns have constraints.
INFORMATION_SCHEMA
Name |
SHOW
Name |
Remarks |
CONSTRAINT_CATALOG |
def |
|
CONSTRAINT_SCHEMA |
||
CONSTRAINT_NAME |
||
TABLE_CATALOG |
def |
|
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_POSITIONis 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_CONSTRAINTisNULLfor 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
t1andt3that 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_USAGEtable 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.
User Comments
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.