尝试将多个外键添加到单个表时出错

Question

我正试图创建一个子表，约束来自父表的3个外键，但我收到一个错误1215：无法添加外键约束

父表：

CREATE TABLE `Availability` (
`time_of_day` varchar(20) NOT NULL,
`day_of_week` varchar(20) NOT NULL,
`email` varchar(60) NOT NULL,
PRIMARY KEY (`time_of_day`,`day_of_week`,`email`),
KEY `email` (`email`),
CONSTRAINT `Availability_ibfk_1` FOREIGN KEY (`email`) REFERENCES `service_provider` (`email_service_provider`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

子表(由于上面提到的错误，我无法构建(：

CREATE TABLE TEST1
(
num_request INT NOT NULL,
time_of_day VARCHAR(20) NOT NULL,
day_of_week VARCHAR(20) NOT NULL,
email VARCHAR(60) NOT NULL,
PRIMARY KEY (num_request),
Foreign key (time_of_day) references Availability(time_of_day),
Foreign key (day_of_week) references Availability(day_of_week),
Foreign key (email) references Availability(email)
);

请告诉我我做错了什么。。。谢谢大家。

Answer 1

当您使用复合主键(即多列键(为表创建外键时，也应该使外键复合。

CREATE TABLE TEST1
(
num_request INT NOT NULL,
time_of_day VARCHAR(20) NOT NULL,
day_of_week VARCHAR(20) NOT NULL,
email VARCHAR(60) NOT NULL,
PRIMARY KEY (num_request),
Foreign key (time_of_day, day_of_week, email) references Availability(time_of_day, day_of_week, email)
)

外键的列应该与它们引用的主键或唯一键的列相匹配。它们应该具有相同数量的列，顺序相同。

您尝试创建三个单独的约束，每个约束一列。