Description:
For each database, MySQL seems to have a on namespace for unique/primary key constraints, and one namespace for foreign key constraints (is it documented?).
This allows creation of a table containing a unique constraint and a foreign key constraint with identical constraint names.
I'd expect to have one namespace for all table constraints. Although this feature does not impair the functionality of constraints, it does make it harder to query the information_schema correctly. Examples:
To identify a constraint in information_schema.TABLE_CONSTRAINTS, one MUST use a key including CONSTRAINT_TYPE. (Other columns: CONSTRAINT_SCHEMA or TABLE_SCHEMA, TABLE_NAME, CONSTAINT_NAME). ISO 9075 specifies that (in absence of catalogs) CONSTRAINT_SCHEMA and CONSTRAINT_NAME should suffice.
Because KEY_COLUMN_USAGE does not contain a CONSTRAINT_TYPE column, one cannot easily identify the corresponding record in TABLE_CONSTRAINTS. To Join between KEY_COLUMN_USAGE and TABLE_CONSTRAINTS, one is always forced to explicitly specify a value for CONSTRAINT_TYPE. One can see when this is needed, because in such a case either of the REFERENCED% columns will be NOT NULL. However, these are NON-standard columns.
How to repeat:
mysql> create database db
-> ;
Query OK, 1 row affected (0.00 sec)
mysql> use db;
Database changed
mysql> create table t(
-> c1 int
-> ,c2 int
-> );
Query OK, 0 rows affected (0.14 sec)
mysql> alter table t add constraint cons1 unique(c1);
Query OK, 0 rows affected (0.20 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index i on t(c2);
Query OK, 0 rows affected (0.58 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table t add constraint cons1 foreign key(c2) references t(c1);
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select table_name
-> , constraint_name
-> , column_name
-> , referenced_column_name
-> from information_schema.key_column_usage
-> where table_schema = 'db'
-> ;
+------------+-----------------+-------------+------------------------+
| table_name | constraint_name | column_name | referenced_column_name |
+------------+-----------------+-------------+------------------------+
| t | cons1 | c1 | NULL |
| t | cons1 | c2 | c1 |
+------------+-----------------+-------------+------------------------+
2 rows in set (0.00 sec)
Suggested fix:
Either document this behaviour, and the impact it has on querying the information schema, or use a single namespace for all table constraints according to 1so 9075 standard.
(One can derive the intention of iso 9075 concerning this by looking at the primary key definition of definition_schema.table_constraints, i.e. CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME. not CONSTRAINT_TYPE)
Description: For each database, MySQL seems to have a on namespace for unique/primary key constraints, and one namespace for foreign key constraints (is it documented?). This allows creation of a table containing a unique constraint and a foreign key constraint with identical constraint names. I'd expect to have one namespace for all table constraints. Although this feature does not impair the functionality of constraints, it does make it harder to query the information_schema correctly. Examples: To identify a constraint in information_schema.TABLE_CONSTRAINTS, one MUST use a key including CONSTRAINT_TYPE. (Other columns: CONSTRAINT_SCHEMA or TABLE_SCHEMA, TABLE_NAME, CONSTAINT_NAME). ISO 9075 specifies that (in absence of catalogs) CONSTRAINT_SCHEMA and CONSTRAINT_NAME should suffice. Because KEY_COLUMN_USAGE does not contain a CONSTRAINT_TYPE column, one cannot easily identify the corresponding record in TABLE_CONSTRAINTS. To Join between KEY_COLUMN_USAGE and TABLE_CONSTRAINTS, one is always forced to explicitly specify a value for CONSTRAINT_TYPE. One can see when this is needed, because in such a case either of the REFERENCED% columns will be NOT NULL. However, these are NON-standard columns. How to repeat: mysql> create database db -> ; Query OK, 1 row affected (0.00 sec) mysql> use db; Database changed mysql> create table t( -> c1 int -> ,c2 int -> ); Query OK, 0 rows affected (0.14 sec) mysql> alter table t add constraint cons1 unique(c1); Query OK, 0 rows affected (0.20 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index i on t(c2); Query OK, 0 rows affected (0.58 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table t add constraint cons1 foreign key(c2) references t(c1); Query OK, 0 rows affected (0.21 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select table_name -> , constraint_name -> , column_name -> , referenced_column_name -> from information_schema.key_column_usage -> where table_schema = 'db' -> ; +------------+-----------------+-------------+------------------------+ | table_name | constraint_name | column_name | referenced_column_name | +------------+-----------------+-------------+------------------------+ | t | cons1 | c1 | NULL | | t | cons1 | c2 | c1 | +------------+-----------------+-------------+------------------------+ 2 rows in set (0.00 sec) Suggested fix: Either document this behaviour, and the impact it has on querying the information schema, or use a single namespace for all table constraints according to 1so 9075 standard. (One can derive the intention of iso 9075 concerning this by looking at the primary key definition of definition_schema.table_constraints, i.e. CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME. not CONSTRAINT_TYPE)