information_schema.key_column_usage does not contain information about FKs

Bug #654219 reported by Marcus Eriksson on 2010-10-03
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Drizzle
Critical
Brian Aker
7.0
Critical
Brian Aker

Bug Description

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

Related branches

Monty Taylor (mordred) on 2010-10-05
Changed in drizzle:
assignee: nobody → Brian Aker (brianaker)
Brian Aker (brianaker) wrote :

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) wrote :

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

Brian Aker (brianaker) wrote :

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

Brian Aker (brianaker) wrote :

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.

Changed in drizzle:
importance: Undecided → Critical
Lee Bieber (kalebral) on 2010-10-08
Changed in drizzle:
status: New → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers