24.3.12 The INFORMATION_SCHEMA KEY_COLUMN_USAGE Table (original) (raw)

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 andt3 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, theKEY_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.