mysql - 错误1215，无法添加外键约束

Question

create database blood_bank;
use blood_bank;
create table Employee(
    Emp_id integer not null,
    Emp_name char(20) not null,
    phone integer,
    address char(20),
    constraint primary key(Emp_id)
);

create table WorkInStorage(
    Emp_id integer not null,
    since date not null,
    emp_salary integer,
    constraint primary key(Emp_id),
    constraint foreign key(Emp_id) references Employee(Emp_id) on delete cascade
);

create table Reports_To(
    supervisor_id integer not null,
    subordinate_id integer not null,
    constraint primary key (supervisor_id,subordinate_id),
    constraint foreign key(subordinate_id) references Employee(Emp_id),
    constraint foreign key(supervisor_id) references Employee(Emp_id)
);

create table Nurse(
    nurse_salary integer ,
    Emp_id integer not null,
    constraint primary key(Emp_id),
    constraint foreign key(Emp_id) references Employee(Emp_id) on delete cascade
);

create table Receptionist(
    Receptionist_salaray integer,
    Emp_id integer not null,
    constraint primary key(Emp_id),
    constraint foreign key(Emp_id) references Employee(Emp_id) on delete cascade

);

create table donor(
    donor_id integer not null,
    donor_address char(20),
    birthdate date,
    donor_name char(20),
    donor_gender char(5),
    donor_phone integer,
    Emp_id integer not null,
    blood_id integer not null,
    constraint primary key(donor_id),
    constraint foreign key(Emp_id)references Nurse(Emp_id) on delete cascade ,
    constraint foreign key(Emp_id)references Receptionist(Emp_id) on delete cascade ,
    constraint foreign key(blood_id)references Blood(blood_id)on delete cascade

);
create table Checks(
    isQualified boolean not null,
    donor_id integer,
    donor_address char(20),
    birthdate date,
    donor_name char(20),
    donor_gender boolean,
    donor_phone integer,
    Emp_id integer not null,
    constraint primary key(donor_id),
    constraint foreign key(Emp_id) references Nurse(Emp_id)  
);

当我尝试执行此查询时，它说它无法在 donor 表中添加外键。

create table donor(  donor_id integer not null,  donor_address char(20),  birthdate date,  donor_name char(20),  donor_gender char(5),  donor_phone integer,  Emp_id integer not null,  blood_id integer not null,  constraint primary key(donor_id),  constraint foreign key(Emp_id)references Nurse(Emp_id) on delete cascade ,  constraint foreign key(Emp_id)references Receptionist(Emp_id) on delete cascade ,  constraint foreign key(blood_id)references Blood(blood_id)on delete cascade  )    

Error Code: 1215. Cannot add foreign key constraint

Answer 1

如果您要求在数据库不存在的情况下运行该脚本，那么您将收到错误 1215，因为您的表 blood 不存在。

为了证明这一点，如果我执行测试

create table blood
(   blood_id int auto_increment primary key
);

然后尝试创建表donor，它起作用了。