Description:
The 5.1.11 information_schema contains the REFERENTIAL_CONSTRAINTS system view. This is
intended to contain a row for each foreign key constraint.
The SQL Standard assumes that the columns of a foreign key always reference the columns
of either a PRIMARY KEY or UNIQUE constraint. In that sense, a foreign key references a
PRIMARY KEY or UNIQUE constraint. The UNIQUE_CONSTRAINT_% columns in the
REFERENTIAL_CONSTRAINTS view are intended to identify that constraint.
However, in the MySQL implementation, the UNIQUE_CONSTRAINT_NAME column does not list the
name of any constraint. Instead, it lists the name of the table that is referenced by the
foreign key. At least, comparing the data from RERENTIAL_CONSTRAINTS with the data in
TABLE_CONSTRAINTS reveals inconsistency.
This means that it is completely impossible to find out if the columns of a foreign key
constraint reference the columns of UNIQUE or PRIMARY KEY constraint. It is important to
be able to do that. The REFERENTIAL_CONSTRAINTS system view could potentially offer
valuable information to a user that wants to drop or alter a UNIQUE or PRIMARY KEY
constraint, but only if the UNIQUE_CONSTRAINT_NAME column lists the actual name of the
constraint. The table name of the referenced table is useful too, but it is already
possible to query that information from the KEY_COLUMN_USAGE system view
(REFERENCED_TABLE_NAME).
How to repeat:
-- all queries in the test database
-- ---------------------------------
use test;
create table t1 (
c1 varchar(10)
, c2 date
, primary key (c1,c2)
) engine=innodb;
create table t2 (
c1 varchar(10)
, c2 date
, foreign key (c1,c2)
references t1 (c1,c2)
) engine=innodb;
-- constraint name as far as TABLE_CONSTRAINTS is concerned
-- --------------------------------------------------------
select table_name
, constraint_type
, constraint_name
from information_schema.table_constraints
where table_schema = schema()
and table_name in ('t1,'t2');
+------------+-----------------+-----------------+
| table_name | constraint_type | constraint_name |
+------------+-----------------+-----------------+
| t1 | PRIMARY KEY | PRIMARY |
| t2 | FOREIGN KEY | t2_ibfk_1 |
+------------+-----------------+-----------------+
2 rows in set (0.12 sec)
-- results from REFERENTIAL_CONSTRAINTS
-- ------------------------------------
select constraint_name
, unique_constraint_name
from information_schema.referential_constraints
where constraint_schema = schema()
and constraint_name = 't2_ibfk_1'
;
+-----------------+------------------------+
| constraint_name | unique_constraint_name |
+-----------------+------------------------+
| t2_ibfk_1 | t1 |
+-----------------+------------------------+
1 row in set (0.00 sec)
It was expected that the value 'PRIMARY' would be given in the UNIQUE_CONSTRAINT_NAME
column, not 't1'. At any rate, the same value should be given in the
UNIQUE_CONSTRAINT_NAME column as appeared in the prior query on the TABLE_CONSTAINTS
system view.
When a slightly modified version of t1 is used,
create table t1 (
c1 varchar(10)
, c2 date
, unique (c1,c2)
) engine=innodb;
(defining a UNIQUE constraint instead of a PRIMARY KEY), similar results are obtained:
the UNIQUE_CONSTRAINT_NAME column lists the table name of the referenced table, not the
constraint name as it appears in TABLE_CONSTRAINTS.
This is even the case when the unique constraint is explicitly given a name as in:
create table t1 (
c1 varchar(10)
, c2 date
, constraint uk_t1 unique (c1,c2)
) engine=innodb;
Suggested fix:
In case *all* the columns in the foreign key reference *all* the columns of a UNIQUE or
PRIMARY KEY constraint, explicitly list the name of the referenced constraint. This is
the value as it appears in the CONSTRAINT_NAME column of the TABLE_CONSTRAINTS system
view - not - the name of the table.
In addition, because the constraint name is not sufficient to identify the actual
constraint (the combination of UNIQUE_CONSTRAINT_SCHEMA and UNIQUE_CONSTRAINT_NAME does
not uniquely identify a single constraint) it seems sensible to add a non-standard column
that contains the name of the referenced table, for example: REFERENCED_TABLE_NAME
(matches a non standard column in KEY_COLUMN_USAGE). That way,
(UNIQUE_CONSTRAINT_SCHEMA,UNIQUE_CONSTRAINT_NAME,REFERENCED_TABLE_NAME) can be used to
uniquely identify the referenced constraint.
I am aware that InnoDB allows foreign keys to be created that do not reference all the
columns in a unique or primary key constraint. In fact innodb is happy as long as all the
columns in the foreign key definition can be matched to the first and consecutive columns
of an index on the referenced table (even if that is not an unique index, and even if not
all of the columns in the index are referenced)
It is hard to come up with a good value for the UNIQUE_CONSTRAINT_NAME in these cases.
Listing the name of the index in the UNIQUE_CONSTRAINT_NAME column in this case is IMO
not desirable. It is inappropriate for three reasons:
1) there is no corresponding constraint
2) the corresponding referenced index need not be unique
3) even if the corresponding index is unique (or a PRIMARY KEY or UNIQUE constraint are
involved), not all of it's columns need to be referenced, in which case the reference
likely does not identify a unique set or rows in the referenced table.
To remedy this issue, I would suggest to add a non-standard column
REFERENCED_COLUMN_NAMES that contains a comma separated list of the (quoted) column names
that are referenced by the foreign key. With such a column in place, the
UNIQUE_CONSTRAINT_NAME could just report a NULL for all the cases where the foreign key
references a non-unique combination of columns.
The concatenation of the referenced column names in combination with the other proposed
non-standard column REFERENCED_TABLE_NAME sufficiently identifies the correspondence
between the foreign key and the referenced index.
Description: The 5.1.11 information_schema contains the REFERENTIAL_CONSTRAINTS system view. This is intended to contain a row for each foreign key constraint. The SQL Standard assumes that the columns of a foreign key always reference the columns of either a PRIMARY KEY or UNIQUE constraint. In that sense, a foreign key references a PRIMARY KEY or UNIQUE constraint. The UNIQUE_CONSTRAINT_% columns in the REFERENTIAL_CONSTRAINTS view are intended to identify that constraint. However, in the MySQL implementation, the UNIQUE_CONSTRAINT_NAME column does not list the name of any constraint. Instead, it lists the name of the table that is referenced by the foreign key. At least, comparing the data from RERENTIAL_CONSTRAINTS with the data in TABLE_CONSTRAINTS reveals inconsistency. This means that it is completely impossible to find out if the columns of a foreign key constraint reference the columns of UNIQUE or PRIMARY KEY constraint. It is important to be able to do that. The REFERENTIAL_CONSTRAINTS system view could potentially offer valuable information to a user that wants to drop or alter a UNIQUE or PRIMARY KEY constraint, but only if the UNIQUE_CONSTRAINT_NAME column lists the actual name of the constraint. The table name of the referenced table is useful too, but it is already possible to query that information from the KEY_COLUMN_USAGE system view (REFERENCED_TABLE_NAME). How to repeat: -- all queries in the test database -- --------------------------------- use test; create table t1 ( c1 varchar(10) , c2 date , primary key (c1,c2) ) engine=innodb; create table t2 ( c1 varchar(10) , c2 date , foreign key (c1,c2) references t1 (c1,c2) ) engine=innodb; -- constraint name as far as TABLE_CONSTRAINTS is concerned -- -------------------------------------------------------- select table_name , constraint_type , constraint_name from information_schema.table_constraints where table_schema = schema() and table_name in ('t1,'t2'); +------------+-----------------+-----------------+ | table_name | constraint_type | constraint_name | +------------+-----------------+-----------------+ | t1 | PRIMARY KEY | PRIMARY | | t2 | FOREIGN KEY | t2_ibfk_1 | +------------+-----------------+-----------------+ 2 rows in set (0.12 sec) -- results from REFERENTIAL_CONSTRAINTS -- ------------------------------------ select constraint_name , unique_constraint_name from information_schema.referential_constraints where constraint_schema = schema() and constraint_name = 't2_ibfk_1' ; +-----------------+------------------------+ | constraint_name | unique_constraint_name | +-----------------+------------------------+ | t2_ibfk_1 | t1 | +-----------------+------------------------+ 1 row in set (0.00 sec) It was expected that the value 'PRIMARY' would be given in the UNIQUE_CONSTRAINT_NAME column, not 't1'. At any rate, the same value should be given in the UNIQUE_CONSTRAINT_NAME column as appeared in the prior query on the TABLE_CONSTAINTS system view. When a slightly modified version of t1 is used, create table t1 ( c1 varchar(10) , c2 date , unique (c1,c2) ) engine=innodb; (defining a UNIQUE constraint instead of a PRIMARY KEY), similar results are obtained: the UNIQUE_CONSTRAINT_NAME column lists the table name of the referenced table, not the constraint name as it appears in TABLE_CONSTRAINTS. This is even the case when the unique constraint is explicitly given a name as in: create table t1 ( c1 varchar(10) , c2 date , constraint uk_t1 unique (c1,c2) ) engine=innodb; Suggested fix: In case *all* the columns in the foreign key reference *all* the columns of a UNIQUE or PRIMARY KEY constraint, explicitly list the name of the referenced constraint. This is the value as it appears in the CONSTRAINT_NAME column of the TABLE_CONSTRAINTS system view - not - the name of the table. In addition, because the constraint name is not sufficient to identify the actual constraint (the combination of UNIQUE_CONSTRAINT_SCHEMA and UNIQUE_CONSTRAINT_NAME does not uniquely identify a single constraint) it seems sensible to add a non-standard column that contains the name of the referenced table, for example: REFERENCED_TABLE_NAME (matches a non standard column in KEY_COLUMN_USAGE). That way, (UNIQUE_CONSTRAINT_SCHEMA,UNIQUE_CONSTRAINT_NAME,REFERENCED_TABLE_NAME) can be used to uniquely identify the referenced constraint. I am aware that InnoDB allows foreign keys to be created that do not reference all the columns in a unique or primary key constraint. In fact innodb is happy as long as all the columns in the foreign key definition can be matched to the first and consecutive columns of an index on the referenced table (even if that is not an unique index, and even if not all of the columns in the index are referenced) It is hard to come up with a good value for the UNIQUE_CONSTRAINT_NAME in these cases. Listing the name of the index in the UNIQUE_CONSTRAINT_NAME column in this case is IMO not desirable. It is inappropriate for three reasons: 1) there is no corresponding constraint 2) the corresponding referenced index need not be unique 3) even if the corresponding index is unique (or a PRIMARY KEY or UNIQUE constraint are involved), not all of it's columns need to be referenced, in which case the reference likely does not identify a unique set or rows in the referenced table. To remedy this issue, I would suggest to add a non-standard column REFERENCED_COLUMN_NAMES that contains a comma separated list of the (quoted) column names that are referenced by the foreign key. With such a column in place, the UNIQUE_CONSTRAINT_NAME could just report a NULL for all the cases where the foreign key references a non-unique combination of columns. The concatenation of the referenced column names in combination with the other proposed non-standard column REFERENCED_TABLE_NAME sufficiently identifies the correspondence between the foreign key and the referenced index.