MySQL 5.1 Database Reference Guide :: 23.13 The INFORMATION_SCHEMA KEY_COLUMN

23.13. The `INFORMATION_SCHEMA KEY_COLUMN_USAGE` Table

$8.99

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.

For example, 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.