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.

Answer 1

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.