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: | |
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
[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