Bug #21713 incorrect value for the REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME column
Submitted: 18 Aug 2006 9:23 Modified: 14 Feb 2007 16:41
Reporter: Roland Bouman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Information schema Severity:S3 (Non-critical)
Version:5.1.12-BK, 5.1.11 beta OS:Linux (Ubuntu 6 (Linux 2.6.15))
Assigned to: Sergey Glukhov CPU Architecture:Any
Tags: constraint, foreign key, information_schema, Q1, REFERENTIAL_CONSTRAINTS, TABLE_CONSTRAINTS

[18 Aug 2006 9:23] Roland Bouman
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.
[26 Aug 2006 9:12] Valeriy Kravchuk
Thank you for a bug report. Verified with 5.1.12-BK. Content of UNIQUE_CONSTRAINT_NAME is not a name of constraint, and it does not identify the referenced key by name (table can have 2 unique keys...), so it is a bug.
[20 Dec 2006 14:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17221

ChangeSet@1.2361, 2006-12-20 18:44:49+04:00, gluh@mysql.com +5 -0
  Bug#21713 incorrect value for the REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME column
  added new field 'REFERENCED_TABLE_NAME' to 'referential_constraints' table
  field 'UNIQUE_CONSTRAINT_NAME' contains the name of the referenced index
[10 Jan 2007 13:33] Alexander Barkov
The patch looks ok to push
[15 Jan 2007 9:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/18108

ChangeSet@1.2391, 2007-01-15 13:39:28+04:00, gluh@mysql.com +5 -0
  Bug#21713 incorrect value for the REFERENTIAL_CONSTRAINTS.UNIQUE_CONSTRAINT_NAME column
  added new field 'REFERENCED_TABLE_NAME' to 'referential_constraints' table
  field 'UNIQUE_CONSTRAINT_NAME' contains the name of the referenced index
[28 Jan 2007 2:29] Igor Babaev
The fix has been pushed into 5.1.16-beta main tree.
[14 Feb 2007 16:41] Paul Dubois
Noted in 5.1.16 changelog.

In the INFORMATION_SCHEMA REFERENTIAL_CONSTRAINTS table, the
UNIQUE_CONSTRAINT_NAME column incorrectly named the referenced table.
Now it names the referenced constraint, and a new column, 
REFERENCED_TABLE_NAME, names the referenced table.

Also added the new column to the REFERENTIAL_CONSTRAINTS
table section.