information_schema.key_column_usage does not contain information about FKs

[Marcus Eriksson]

CREATE TABLE `prim_key` (
  `id` INT NOT NULL,
  `val` VARCHAR(20) COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `fore_key0` (
  `id` INT NOT NULL,
  `id_ref0` INT DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_ref0` (`id_ref0`),
  CONSTRAINT `fore_key0_ibfk_1` FOREIGN KEY (`id_ref0`) REFERENCES `prim_key` (`id`)
);
 CREATE TABLE `fore_key1` (
  `id` INT NOT NULL,
  `id_ref1` INT DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_ref1` (`id_ref1`),
  CONSTRAINT `fore_key1_ibfk_1` FOREIGN KEY (`id_ref1`) REFERENCES `prim_key` (`id`) ON UPDATE CASCADE
);

then:
select * from information_schema.key_column_usage;
+--------------------+-------------------+-----------------+---------------+-----------------+------------+-------------+------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION |
+--------------------+-------------------+-----------------+---------------+-----------------+------------+-------------+------------------+
| | test_units_jdbc | PRIMARY | | test_units_jdbc | prim_key | id | 0 |
| | test_units_jdbc | PRIMARY | | test_units_jdbc | fore_key0 | id | 0 |
| | test_units_jdbc | PRIMARY | | test_units_jdbc | fore_key1 | id | 0 |
+--------------------+-------------------+-----------------+---------------+-----------------+------------+-------------+------------------+
3 rows in set (0 sec)

According to postgresql information_schema documentation, (http://www.postgresql.org/docs/8.1/interactive/infoschema-key-column-usage.html), all primary, foreign and unique constraints should be included

[Brian Aker (brianaker)]

Additional test:
http://dev.mysql.com/doc/refman/5.6/en/key-column-usage-table.html

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;

[Brian Aker (brianaker)]

(Note on MySQL test case, their result set doesn't follow the standard in regards to columns made available).

[Brian Aker (brianaker)]

And another test case (from SQL Server).
http://support.microsoft.com/kb/278387

[Brian Aker (brianaker)]

One note, we don't currently display a constraint name if we made it out "PRIMARY KEY", we should probably fix this, though it may take some major surgery because of the way that internally we use the first unique key as the primary key.