mysql - 错误1005(HY000): Can't create table 'test_schema.#sql-44c_2a' (errno: 150)

Question

因此，我用更具可读性的代码重现了我遇到的问题。我在 mysql 提示符下运行这个 sql 文件:

CREATE SCHEMA IF NOT EXISTS test_schema 
DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;

USE test_schema ;

-- -----------------------------------------------------
-- Table test_schema.table_one
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS table_one (
    table_one_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    table_one_name VARCHAR(45) NOT NULL,
    PRIMARY KEY (table_one_id, table_one_name)
)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table test_schema.table_two
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS table_two (
    table_two_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    table_two_name VARCHAR(45) NOT NULL,
    table_one_name VARCHAR(45) NOT NULL,
    PRIMARY KEY (table_two_id)
)
ENGINE = InnoDB;

ALTER TABLE table_two ADD FOREIGN KEY (table_one_name) REFERENCES table_one(table_one_name);

我从提示中得到的输出是:

mysql> source test-database.sql;
Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.07 sec)

Query OK, 0 rows affected (0.08 sec)

ERROR 1005 (HY000): Can't create table 'test_schema.#sql-44c_2a' (errno: 150)

如果我运行“SHOW ENGINE INNODB STATUS;”我得到以下详细信息

------------------------
LATEST FOREIGN KEY ERROR
------------------------
150603  9:22:25 Error in foreign key constraint of table test_schema/#sql-44c_2a:
FOREIGN KEY (table_one_name) REFERENCES table_one(table_one_name):
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.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

我搜索过这个问题，几乎每个人都说它是关于:
1) 没有相同的类型
- 它们都是“VARCHAR(45) NOT NULL”(尝试让 table_two 中的那个为空，没有改变错误消息)
2)外键不是主键
- table_one_name 是 table_one 中的主键，与 table_one_id 一起
3) 确保表级别的字符集和整理选项相同
- 这是什么意思？我想它们是因为我不改变它们？

请帮忙//芬贝尔

Answer 1

为了添加外键，必须对两个字段进行索引。

试试这个:

-- -----------------------------------------------------
-- Table test_schema.table_one
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS table_one (
    table_one_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    table_one_name VARCHAR(45) NOT NULL,
    PRIMARY KEY (table_one_id, table_one_name),
    KEY `one_name` (`table_one_name`)
)
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table test_schema.table_two
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS table_two (
    table_two_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    table_two_name VARCHAR(45) NOT NULL,
    table_one_name VARCHAR(45) NOT NULL,
    PRIMARY KEY (table_two_id),
    KEY `two_name` (`table_two_name`)
)
ENGINE = InnoDB;

ALTER TABLE table_two  ADD FOREIGN KEY (table_one_name) REFERENCES table_one(table_one_name);