Bug #38997 Multicolumn FKs behave differently than what is suggested in the docs
Submitted: 25 Aug 2008 6:59 Modified: 29 Sep 2009 2:13
Reporter: Peter Rabbitson Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.51, 4.1, 5.0, 5.1, 6.0 bzr OS:Linux (Stock Debian/SID package)
Assigned to: Jimmy Yang CPU Architecture:Any
Tags: constraint, fk, improper, INDEX, Use

[25 Aug 2008 6:59] Peter Rabbitson
Description:
Hi,

There is some weirdness on how mysql deals with columns containing both referencING data and referencED data. I have read and re-read the requirements for constraint composition (http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html) and I do not see anything that indicates what I am doing below shouldn't work. What's more, the example that does work makes no sense whatsoever, and I am afraid that the constraint is actually broken.

If this is not a bug, but intended behavior, I would be happy to hear a detailed explanation. 
Thank you for your help!

P.S. The reason to jump through these hoops, is that I need to enforce a unique index on articles_local as seen in the example below.

How to repeat:
Here is a copy of an entire session, starting from the creation of an empty database. 

mysql> create database const_test;
Query OK, 1 row affected (0.00 sec)

mysql> use const_test;
Database changed
mysql> CREATE TABLE `components` (`id` SMALLINT NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE `articles` (`id` BIGINT NOT NULL auto_increment,`component_id` SMALLINT NOT NULL,  PRIMARY KEY (`id`), CONSTRAINT `articles_fk_component_id` FOREIGN KEY (`component_id`) REFERENCES `components` (`id`) ON DELETE RESTRICT ON UPDATE CASCADE) ENGINE=InnoDB;Query OK, 0 rows affected (0.18 sec)

===============================
This create misteriously FAILS
===============================
mysql> CREATE TABLE `articles_local` (`id` BIGINT NOT NULL auto_increment, `article_id` BIGINT NOT NULL, `component_id` SMALLINT NOT NULL,  `name` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE `u_name` (`name`, `component_id`), CONSTRAINT `articles_local_fk_article_id_component_id` FOREIGN KEY (`article_id`, `component_id`) REFERENCES `articles` (`id`, `component_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;ERROR 1005 (HY000): Can't create table './const_test/articles_local.frm' (errno: 150)
mysql> 

----------------------------------------------------------
LATEST FOREIGN KEY ERROR (produced by SHOW INNODB STATUS)
----------------------------------------------------------
080825  8:42:39 Error in foreign key constraint of table const_test/articles_local:
 FOREIGN KEY (`article_id`, `component_id`) REFERENCES `articles` (`id`, `component_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

=========================================================================
This PASSES with flying colors (the constraint column order is reversed)
=========================================================================
mysql> CREATE TABLE `articles_local` (`id` BIGINT NOT NULL auto_increment, `article_id` BIGINT NOT NULL, `component_id` SMALLINT NOT NULL,  `name` VARCHAR(255) NOT NULL, PRIMARY KEY (`id`), UNIQUE `u_name` (`name`, `component_id`), CONSTRAINT `articles_local_fk_article_id_component_id` FOREIGN KEY (`component_id`, `article_id`) REFERENCES `articles` (`component_id`, `id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
Query OK, 0 rows affected (0.22 sec)

In the attached file you can find queries showing the state of the database after this statement.
[25 Aug 2008 7:03] Peter Rabbitson
Table description queries

Attachment: table_info.txt (text/plain), 8.93 KiB.

[25 Aug 2008 9:05] Sveta Smirnova
Thank you for the report.

Verified as described. Falcon is not affected.
[26 Aug 2008 1:44] Timothy Smith
Workaround: re-order columns in REFERENCES clause
[29 Sep 2009 2:13] Jimmy Yang
This is not a bug. However, I see there are places that could cause confusion, since there is a hidden index column added druing create index (for foreign key constraint) that user might not be aware of.

For the table 'article', when foreign key constrain 'articles_fk_component_id" is created, an index with the same name is also created. The constraint/index is on column "component_id". However, a hidden index column on "id" is added to this index (so index is on "component_id" and "id"):

dict_index_build_internal_non_clust() {
...
        /* Add to new_index the columns necessary to determine the clustered
        index entry uniquely */

        for (i = 0; i < clust_index->n_uniq; i++) {

                field = dict_index_get_nth_field(clust_index, i);

                if (!indexed[field->col->ind]) {
                        dict_index_add_col(new_index, table, field->col,
                                           field->prefix_len);
                }
        }
...
}

((gdb) down
#0  dict_index_add_col (index=0x2aaaaadbb8b8, table=0x2aaaaadb58b8,
    col=0x2aaaaadb28c8, prefix_len=0) at dict/dict0dict.c:1507
1507            col_name = dict_table_get_col_name(table, dict_col_get_no(col));
(gdb) n
1509            dict_mem_index_add_field(index, col_name, prefix_len);
(gdb) print col_name
$115 = 0x2aaaaadb2950 "id"

(gdb) print index->fields[0]
$118 = {col = 0x2aaaaadb28d0, name = 0x2aaaaadb2953 "component_id",
  prefix_len = 0, fixed_len = 2}
(gdb) print index->fields[1]
$119 = {col = 0x2aaaaadb28c8, name = 0x2aaaaadb2950 "id", prefix_len = 0,
  fixed_len = 8}

stack:

dict_index_add_col
dict_index_build_internal_non_clust
dict_index_add_to_cache
dict_create_index_step
...
row_create_index_for_mysql
create_index

As a result, there is an index on (component_id, id) on table `articles`.

This explains why after you switch the order of foreign constrain on table `articles_local`, and make it "REFERENCES `articles` (`component_id`, `id`)", the table is created with "flying colors"...

On the other hand, in your first example of creating table `articles_local`, your foreign contrain is defined on table 'articles' and column ('id', 'component_id'). However, there is no index on articles on these two columns in such order. So it violates the requirement there should be an index "in the referenced table where the referenced columns appear as the first columns, or column types in the table", and thus error DB_CANNOT_ADD_CONSTRAINT is returned:

dict_create_foreign_constraints_low()
{

        /* Try to find an index which contains the columns as the first fields
        and in the right order, and the types are the same as in
        foreign->foreign_index */

        if (referenced_table) {
                index = dict_foreign_find_index(referenced_table,
                                                column_names, i,
                                                foreign->foreign_index,
                                                TRUE, FALSE);
                if (!index) {
                        dict_foreign_free(foreign);
                        mutex_enter(&dict_foreign_err_mutex);
                        dict_foreign_error_report_low(ef, name);
                        fprintf(ef, "%s:\n"
                                "Cannot find an index in the"
                                " referenced table where the\n"
                                "referenced columns appear as the"
                                " first columns, or column types\n"
                                "in the table and the referenced table"
                                " do not match for constraint.\n"
                                "Note that the internal storage type of"
                                " ENUM and SET changed in\n"
                               "tables created with >= InnoDB-4.1.12,"
                                " and such columns in old tables\n"
                                "cannot be referenced by such columns"
                                " in new tables.\n"
                                "See http://dev.mysql.com/doc/refman/5.1/en/"
                                "innodb-foreign-key-constraints.html\n"
                                "for correct foreign key definition.\n",
                                start_of_latest_foreign);
                        mutex_exit(&dict_foreign_err_mutex);

                        return(DB_CANNOT_ADD_CONSTRAINT);

...
}

To resolve your problme, you will need to create an index on (`id`, `component_id`) for table 'article', then the foreign constrain can be created in your first example as well:

mysql> create index idx2 on articles(`id`, `component_id`);
Query OK, 0 rows affected (4.91 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `articles_local` (`id` BIGINT NOT NULL auto_increment, `article_id`
    -> BIGINT NOT NULL, `component_id` SMALLINT NOT NULL,  `name` VARCHAR(255) NOT NULL, PRIMARY
    -> KEY (`id`), UNIQUE `u_name` (`name`, `component_id`), CONSTRAINT
    -> `articles_local_fk_article_id_component_id` FOREIGN KEY (`article_id`, `component_id`)
    -> REFERENCES `articles` (`id`, `component_id`) ON DELETE CASCADE ON UPDATE CASCADE)
    -> ENGINE=InnoDB;
Query OK, 0 rows affected (8.33 sec)

So the key here is just as the error message described, you need an index on reference table with its indexed keys(columns) matches intended referenced columns in current table in terms of both order and type.

As explained, accidently, with the added hidden column, the second example where you switch the constraint column order, this requiement got met, and so the create table was successful. So this might cause a bit confusion.

thanks
Jimmy