mysql cannot add foreign key #1215

Question

I have read lots of answered questions on SO about this error, but none of the answers seemed to help me solve the problem.

The error I get is

#1215 - Cannot add foreign key constraint

and when I do show engine innodb status, it gives me this information:

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.

Here are the tables involved (I am making a creation script, and the error comes up during the execution of the sql)

CREATE TABLE IF NOT EXISTS customer_type (
    customer_type_id int(11) unsigned NOT NULL AUTO_INCREMENT,
    `type` varchar(128) NOT NULL,
    sort int(11) NOT NULL,
    is_active tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (customer_type_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS location (
    location_id int(11) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(200) NOT NULL,
    email varchar(255) DEFAULT NULL,
    phone varchar(32) DEFAULT NULL,
    address varchar(128) DEFAULT NULL,
    city varchar(255) DEFAULT NULL,
    postal_code varchar(10) DEFAULT NULL,
    shipping_cost float unsigned DEFAULT NULL,
    is_active tinyint(1) NOT NULL DEFAULT '1',
    PRIMARY KEY (location_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_type_location (
    customer_type_id int(11) unsigned NOT NULL,
    location_id int(11) unsigned NOT NULL,
    PRIMARY KEY (customer_type_id,location_id),
    FOREIGN KEY (customer_type_id)
        REFERENCES customer_type(customer_type_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (location_id)
        REFERENCES location(location_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What I don't understand is why it says that I need indexes on the referenced columns, as you can clearly see that the referenced columns are primary keys and should implicitly create an index. So the error doesn't seem to make sense to me.

I have tried most of the suggestions from the answers to this question, but none seem to help.

I have always used PostgreSQL in the past, so I'm not very familiar with most of the MySQL errors, but this project requires me to use MySQL. Any help would be appreciated.

and `show variables where variable_name in ('innodb_version','version_comment')` says? — Drew, Nov 26 '15 at 23:30
I was using a freshly created database, but it looks like when I created the db, the collation was set as `utf8_general_ci`, so I dropped it and created the database with collation `utf8_unicode_ci` and that seems to have fixed it. So the db collation needed to match the table collation, which doesn't seem very clear. Thanks! — Sehael, Nov 26 '15 at 23:37
maybe answer your own question just as you said above, and will sprinkle our appreciation toward it. — Drew, Nov 26 '15 at 23:42
Thanks, it was good to know that it worked for you. That basically narrowed it down to how I created the database. — Sehael, Nov 26 '15 at 23:48

score 8 · Accepted Answer · edited Nov 26 '15 at 23:49

It turns out that the problem was with the database collation. My db collation was set to utf8_general_ci, and it wasn't working. I dropped the database, and created the new one using utf8_unicode_ci and then it worked as expected. So it seems that the database collation and table collation should match.

From the Manual Page entitled Using FOREIGN KEY Constraints, an excerpt:

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.

So the indexes were fine.

mysql cannot add foreign key #1215

1 Answers1