MYSQL 外键，无法创建表(errno :150)

Question

我正在尝试为我的系统构建数据库和表。但我发现如果我不在代码中添加外键。没有错误。我用了很多方法尝试让代码工作，但仍然有错误。

Create table if not exists users_details_one
(
    fname varchar(255),
    lname varchar(255),
    address varchar(255),
    users_email varchar(255),
    users_password varchar(255),
    department varchar(255)
 );

Create table if not exists users_one
(
    users_email varchar(255),
    users_password varchar(255) ,

    FOREIGN KEY (users_email) REFERENCES users_details_one(users_email),

    FOREIGN KEY (users_password) REFERENCES users_details_one(users_password)   
);

Answer 1

您的外键中有一个拼写错误:
FOREIGN KEY (users_password) REFERENCES users_details_one(users_spassword) 应为 FOREIGN KEY (users_password) REFERENCES users_details_one(users_password)

并且您还需要表 users_details_one 中的 users_email 和 users_password 索引，如下所示:

Create table if not exists users_details_one
(
    fname varchar(255),
    lname varchar(255),
    address varchar(255),
    users_email varchar(255),
    users_password varchar(255),
    department varchar(255),
    index (users_email),
    index (users_password)
 );

Create table if not exists users_one
(
    users_email varchar(255),
    users_password varchar(255) ,

    FOREIGN KEY (users_email) REFERENCES users_details_one(users_email),

    FOREIGN KEY (users_password) REFERENCES users_details_one(users_password)   
);

索引不必是唯一的。

from the manual

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.