24.3.12 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table

The KEY_COLUMN_USAGE table describes which key columns have constraints.

The KEY_COLUMN_USAGE table has these columns:

• CONSTRAINT_CATALOG

  The name of the catalog to which the constraint belongs. This value is always def.

• CONSTRAINT_SCHEMA

  The name of the schema (database) to which the constraint belongs.

• CONSTRAINT_NAME

  The name of the constraint.

• TABLE_CATALOG

  The name of the catalog to which the table belongs. This value is always def.

• TABLE_SCHEMA

  The name of the schema (database) to which the table belongs.

• TABLE_NAME

  The name of the table that has the constraint.

• COLUMN_NAME

  The name of the column that has the constraint.

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

• ORDINAL_POSITION

  The column's position within the constraint, not the column's position within the table. Column positions are numbered beginning with 1.

• POSITION_IN_UNIQUE_CONSTRAINT

  NULL for unique and primary-key constraints. For foreign-key constraints, this column is the ordinal position in key of the table that is being referenced.

• REFERENCED_TABLE_SCHEMA

  The name of the schema (database) referenced by the constraint.

• REFERENCED_TABLE_NAME

  The name of the table referenced by the constraint.

• REFERENCED_COLUMN_NAME

  The name of the column referenced by the constraint.

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.