mysql - 从 phpmyadmin 导入时无法添加外键

Question

我有以下 sql 脚本:

use restaurant;
set foreign_key_checks=0;
create table rtable_schedule(
    id int(11) not null,
    rdate date not null,
    start_hour tinyint,
    start_min tinyint,
    end_hour tinyint,
    end_min tinyint,
    foreign key (id,rdate) references rtable(id,reservation_date),
    primary key (id,rdate)
);

但是，当我通过 phpmyadmin 导入它时，出现以下错误:

#1215 - 无法添加外键约束有什么想法可以解决这个问题吗？请注意 rtable 已经存在

Answer 1

Here's MySQL 外键文档是这样说的:

InnoDB permits a foreign key to reference any column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

NDB requires an explicit unique key (or primary key) on any column referenced as a foreign key.

由于 reservation_date 列不是主键的一部分，因此它可能在 rtable 中没有索引。我建议在使用外键创建表之前在reservation_date列上创建一个索引。