MySQL创建表报错‘Key column 'recep_id' doesn't exist’问题咨询

执行以下MySQL表创建语句时，触发错误Key column 'recep_id' doesn't exist。已知RECEPTIONIST表已定义recep_id字段，且BLOOD_BANK与RECEPTIONIST的关系为“一名接待员在一个血库工作”，具体排查及修正如下：

原表创建语句 #

CREATE TABLE DONOR
(
donor_id int,
medical_history varchar(20),
donor_name varchar(50),
blood_group char(2),
address varchar(80),
contact_number int,
PRIMARY KEY (donor_id)
);

CREATE TABLE RECEPTIONIST
(
recep_id varchar(10),
recep_name varchar(50),
phone_number int,
donor_id int,
PRIMARY KEY (recep_id),
FOREIGN KEY (donor_id) REFERENCES DONOR (donor_id)
);

CREATE TABLE HOSPITAL
(
hospital_id varchar(10),
hospital_name varchar(50),
hospital_address varchar(80),
PRIMARY KEY (hospital_id)
);

CREATE TABLE BLOOD_BANK 
(
blood_bank_id varchar(10),
blood_group char(2),
stocks int,
PRIMARY KEY (blood_bank_id),
FOREIGN KEY (recep_id) REFERENCES RECEPTIONIST (recep_id),
FOREIGN KEY (hospital_id) REFERENCES HOSPITAL (hospital_id)
);

CREATE TABLE BLOOD
(
blood_code varchar(10),
blood_group char(2),
expired_date date,
PRIMARY KEY (blood_code)
);

CREATE TABLE PATIENT
(
patient_id varchar(10),
patient_name varchar(50),
contact_number int,
blood_group char(2),
address varchar(80),
PRIMARY KEY(patient_id),
FOREIGN KEY (hospital_id) REFERENCES HOSPITAL (hospital_id)
);

错误原因 #

1. BLOOD_BANK表缺失recep_id字段：外键约束要求当前表必须先存在对应字段，才能关联到其他表的主键。你在BLOOD_BANK的外键声明中引用了recep_id，但该表的字段列表里并未定义这个列，这是触发报错的直接原因。
2. PATIENT表存在同样问题：该表的外键引用了hospital_id，但表内同样没有定义这个字段，执行时也会触发类似报错。

修正后的表创建语句 #

CREATE TABLE DONOR
(
donor_id int,
medical_history varchar(20),
donor_name varchar(50),
blood_group char(2),
address varchar(80),
contact_number int,
PRIMARY KEY (donor_id)
);

CREATE TABLE RECEPTIONIST
(
recep_id varchar(10),
recep_name varchar(50),
phone_number int,
donor_id int,
PRIMARY KEY (recep_id),
FOREIGN KEY (donor_id) REFERENCES DONOR (donor_id)
);

CREATE TABLE HOSPITAL
(
hospital_id varchar(10),
hospital_name varchar(50),
hospital_address varchar(80),
PRIMARY KEY (hospital_id)
);

CREATE TABLE BLOOD_BANK 
(
blood_bank_id varchar(10),
blood_group char(2),
stocks int,
-- 添加与RECEPTIONIST关联的字段，类型与关联表主键一致
recep_id varchar(10),
PRIMARY KEY (blood_bank_id),
FOREIGN KEY (recep_id) REFERENCES RECEPTIONIST (recep_id),
FOREIGN KEY (hospital_id) REFERENCES HOSPITAL (hospital_id)
);

CREATE TABLE BLOOD
(
blood_code varchar(10),
blood_group char(2),
expired_date date,
PRIMARY KEY (blood_code)
);

CREATE TABLE PATIENT
(
patient_id varchar(10),
patient_name varchar(50),
contact_number int,
blood_group char(2),
address varchar(80),
-- 添加与HOSPITAL关联的字段
hospital_id varchar(10),
PRIMARY KEY(patient_id),
FOREIGN KEY (hospital_id) REFERENCES HOSPITAL (hospital_id)
);

内容的提问来源于stack exchange，提问作者gatnaa