mysql 无法添加外键 #1215

Question

我已经阅读了很多有关此错误的已回答问题，但似乎没有一个答案可以帮助我解决问题。

我得到的错误是

#1215 - Cannot add foreign key constraint 

当我执行 show engine innodb status 时，它会给我以下信息:

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.

这里是涉及到的表(我在做创建脚本，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;

我不明白的是为什么它说我需要引用列上的索引，因为您可以清楚地看到引用列是主键并且应该隐式创建索引。所以这个错误对我来说似乎没有意义。

我已经尝试了 this question 的答案中的大部分建议，但似乎没有帮助。

我以前一直用PostgreSQL，所以对MySQL的大部分错误不是很熟悉，但是这个项目需要用到MySQL。任何帮助，将不胜感激。

Answer 1

事实证明问题出在数据库排序规则上。我的数据库排序规则设置为 utf8_general_ci，但它不起作用。我删除了数据库，并使用 utf8_unicode_ci 创建了新数据库，然后它按预期工作了。所以看起来数据库排序规则和表排序规则应该匹配。

来自标题为 Using FOREIGN KEY Constraints 的手册页，摘录:

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.

所以索引没问题。