mysql - #1215 - 无法添加外键约束：/
问题描述
CREATE TABLE bill (
`number` int(255) NOT NULL,
`patient_id` int(255) DEFAULT NULL,
`doctor_charge` varchar(100) NOT NULL,
`medicine_charge` varchar(100) NOT NULL,
`room_charge` varchar(100) NOT NULL,
`nursing_charge` varchar(100) NOT NULL,
`total_amount` varchar(255) NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (number)
);
CREATE TABLE daily_status (
`ID` int(255) NOT NULL,
`patient_id` int(255) DEFAULT NULL,
`date` date NOT NULL,
`medication` varchar(100) CHARACTER SET utf8 NOT NULL,
`body_temp` varchar(100) CHARACTER SET utf8 NOT NULL,
`blood_pressure` varchar(100) CHARACTER SET utf8 NOT NULL,
`heart_beat` varchar(100) CHARACTER SET utf8 NOT NULL,
`diabetes_level` varchar(100) CHARACTER SET utf8 NOT NULL,
`oxygen_level` varchar(100) CHARACTER SET utf8 NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (ID)
);
CREATE TABLE `doctors` (
`id` int(255) NOT NULL,
`name` varchar(50) CHARACTER SET utf8 NOT NULL,
`position` varchar(100) CHARACTER SET utf8 NOT NULL,
`username` varchar(50) CHARACTER SET utf8 NOT NULL,
`email` varchar(100) CHARACTER SET utf8 NOT NULL,
`mob` varchar(50) CHARACTER SET utf8 NOT NULL,
`password` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `guardian` (
`id` int(255) NOT NULL,
`name` varchar(50) CHARACTER SET utf8 NOT NULL,
`position` varchar(100) CHARACTER SET utf8 NOT NULL,
`username` varchar(50) CHARACTER SET utf8 NOT NULL,
`email` varchar(100) CHARACTER SET utf8 NOT NULL,
`mob` varchar(50) CHARACTER SET utf8 NOT NULL,
`password` varchar(50) CHARACTER SET utf8 NOT NULL,
`request` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `nurses` (
`id` int(255) NOT NULL,
`name` varchar(50) CHARACTER SET utf8 NOT NULL,
`position` varchar(100) CHARACTER SET utf8 NOT NULL,
`username` varchar(50) CHARACTER SET utf8 NOT NULL,
`email` varchar(100) CHARACTER SET utf8 NOT NULL,
`mob` varchar(50) CHARACTER SET utf8 NOT NULL,
`password` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE patient (
`ID` int(255) NOT NULL,
`guard_id` int(255) DEFAULT NULL,
`FullName` varchar(50) CHARACTER SET utf8 NOT NULL,
`DOB` date NOT NULL,
`sex` varchar(50) CHARACTER SET utf8 NOT NULL,
`bloodType` varchar(50) CHARACTER SET utf8 NOT NULL,
`phoneNO` varchar(100) CHARACTER SET utf8 NOT NULL,
`dateIN` date NOT NULL,
`dateOut` date NOT NULL,
`viewstatus` varchar(50) NOT NULL,
`requeststatus` varchar(50) CHARACTER SET utf8 NOT NULL,
FOREIGN KEY (guard_id) REFERENCES guardian (id) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (ID)
);
CREATE TABLE report (
`ID` int(255) NOT NULL,
`patient_id` int(255) DEFAULT NULL,
`doctors_id` int(255) DEFAULT NULL,
`upload_id` int(255) DEFAULT NULL,
`number` int(255) NOT NULL,
`description` varchar(300) CHARACTER SET utf8 NOT NULL,
`source` varchar(100) CHARACTER SET utf8 NOT NULL,
`date` date NOT NULL,
`type` varchar(100) CHARACTER SET utf8 NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (doctors_id) REFERENCES doctors (id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (upload_id) REFERENCES upload (id) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (ID)
);
CREATE TABLE `usersidaccess` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`name` varchar(150) NOT NULL,
`position` varchar(15) NOT NULL,
`username` varchar(100) NOT NULL,
`email` varchar(50) NOT NULL,
`mob` bigint(20) NOT NULL,
`password` varchar(50) NOT NULL,
`access` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `upload` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`date` datetime NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
ALTER TABLE `doctors`
ADD PRIMARY KEY (`id`);
ALTER TABLE `guardian`
ADD PRIMARY KEY (`id`);
ALTER TABLE `nurses`
ADD PRIMARY KEY (`id`);
ALTER TABLE `bill`
MODIFY `number` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `daily_status`
MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `doctors`
MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `guardian`
MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `nurses`
MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
ALTER TABLE `patient`
MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `report`
MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
CREATE TABLE doctor_patient (
patient_id INTEGER NOT NULL,
doctors_id INTEGER NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (doctors_id) REFERENCES doctors (id) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (patient_id, doctors_id)
);
CREATE TABLE nurses_status (
nurses_id INTEGER NOT NULL,
status_id INTEGER NOT NULL,
FOREIGN KEY (nurses_id) REFERENCES nurses (id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (status_id) REFERENCES daily_status (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY ( nurses_id, status_id)
);
解决方案
这可能是因为您试图引用尚未创建的表。请重新排列CREATE语句的顺序，使那些没有任何外键的表在前，然后其他表按顺序排列，不会干扰创建外键。
下面是解决方案。您的代码排序不正确，并且在某些外键字段中具有不同的数据类型。
CREATE TABLE `doctors` (
`id` int(255) NOT NULL,
`name` varchar(50) CHARACTER SET utf8 NOT NULL,
`position` varchar(100) CHARACTER SET utf8 NOT NULL,
`username` varchar(50) CHARACTER SET utf8 NOT NULL,
`email` varchar(100) CHARACTER SET utf8 NOT NULL,
`mob` varchar(50) CHARACTER SET utf8 NOT NULL,
`password` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `doctors`
ADD PRIMARY KEY (`id`);
ALTER TABLE `doctors`
MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
CREATE TABLE `guardian` (
`id` int(255) NOT NULL,
`name` varchar(50) CHARACTER SET utf8 NOT NULL,
`position` varchar(100) CHARACTER SET utf8 NOT NULL,
`username` varchar(50) CHARACTER SET utf8 NOT NULL,
`email` varchar(100) CHARACTER SET utf8 NOT NULL,
`mob` varchar(50) CHARACTER SET utf8 NOT NULL,
`password` varchar(50) CHARACTER SET utf8 NOT NULL,
`request` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `guardian`
ADD PRIMARY KEY (`id`);
ALTER TABLE `guardian`
MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
CREATE TABLE `nurses` (
`id` int(255) NOT NULL,
`name` varchar(50) CHARACTER SET utf8 NOT NULL,
`position` varchar(100) CHARACTER SET utf8 NOT NULL,
`username` varchar(50) CHARACTER SET utf8 NOT NULL,
`email` varchar(100) CHARACTER SET utf8 NOT NULL,
`mob` varchar(50) CHARACTER SET utf8 NOT NULL,
`password` varchar(50) CHARACTER SET utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `nurses`
ADD PRIMARY KEY (`id`);
ALTER TABLE `nurses`
MODIFY `id` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;
CREATE TABLE `usersidaccess` (
`id` int(255) NOT NULL AUTO_INCREMENT,
`name` varchar(150) NOT NULL,
`position` varchar(15) NOT NULL,
`username` varchar(100) NOT NULL,
`email` varchar(50) NOT NULL,
`mob` bigint(20) NOT NULL,
`password` varchar(50) NOT NULL,
`access` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `upload` (
`id` int(255) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
`date` datetime NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;
CREATE TABLE patient (
`ID` int(255) NOT NULL,
`guard_id` int(255) DEFAULT NULL,
`FullName` varchar(50) CHARACTER SET utf8 NOT NULL,
`DOB` date NOT NULL,
`sex` varchar(50) CHARACTER SET utf8 NOT NULL,
`bloodType` varchar(50) CHARACTER SET utf8 NOT NULL,
`phoneNO` varchar(100) CHARACTER SET utf8 NOT NULL,
`dateIN` date NOT NULL,
`dateOut` date NOT NULL,
`viewstatus` varchar(50) NOT NULL,
`requeststatus` varchar(50) CHARACTER SET utf8 NOT NULL,
FOREIGN KEY (guard_id) REFERENCES guardian (id) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (ID)
);
ALTER TABLE `patient`
MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
CREATE TABLE bill (
`number` int(255) NOT NULL,
`patient_id` int(255) DEFAULT NULL,
`doctor_charge` varchar(100) NOT NULL,
`medicine_charge` varchar(100) NOT NULL,
`room_charge` varchar(100) NOT NULL,
`nursing_charge` varchar(100) NOT NULL,
`total_amount` varchar(255) NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (number)
);
ALTER TABLE `bill`
MODIFY `number` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
CREATE TABLE daily_status (
`ID` int(255) NOT NULL,
`patient_id` int(255) DEFAULT NULL,
`date` date NOT NULL,
`medication` varchar(100) CHARACTER SET utf8 NOT NULL,
`body_temp` varchar(100) CHARACTER SET utf8 NOT NULL,
`blood_pressure` varchar(100) CHARACTER SET utf8 NOT NULL,
`heart_beat` varchar(100) CHARACTER SET utf8 NOT NULL,
`diabetes_level` varchar(100) CHARACTER SET utf8 NOT NULL,
`oxygen_level` varchar(100) CHARACTER SET utf8 NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (ID)
);
ALTER TABLE `daily_status`
MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
CREATE TABLE report (
`ID` int(255) NOT NULL,
`patient_id` int(255) DEFAULT NULL,
`doctors_id` int(255) DEFAULT NULL,
`upload_id` int(255) DEFAULT NULL,
`number` int(255) NOT NULL,
`description` varchar(300) CHARACTER SET utf8 NOT NULL,
`source` varchar(100) CHARACTER SET utf8 NOT NULL,
`date` date NOT NULL,
`type` varchar(100) CHARACTER SET utf8 NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (doctors_id) REFERENCES doctors (id) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (ID)
);
ALTER TABLE `report`
MODIFY `ID` int(255) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
ALTER TABLE `report`
ADD CONSTRAINT upload_fk FOREIGN KEY (upload_id) REFERENCES upload (id) ON DELETE RESTRICT ON UPDATE CASCADE;
CREATE TABLE doctor_patient (
patient_id INTEGER NOT NULL,
doctors_id INTEGER NOT NULL,
FOREIGN KEY (patient_id) REFERENCES patient (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (doctors_id) REFERENCES doctors (id) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY (patient_id, doctors_id)
);
CREATE TABLE nurses_status (
nurses_id INTEGER NOT NULL,
status_id INTEGER NOT NULL,
FOREIGN KEY (nurses_id) REFERENCES nurses (id) ON DELETE RESTRICT ON UPDATE CASCADE,
FOREIGN KEY (status_id) REFERENCES daily_status (ID) ON DELETE RESTRICT ON UPDATE CASCADE,
PRIMARY KEY ( nurses_id, status_id)
);
推荐阅读
- sql - 在使用 Installshield 安装期间，如何连接到现有的 Oracle 数据库？
- mysql - 更改类型字段后列的数据被截断
- python-3.x - 如何使用matplotlib绘制excel大数据集而不是从第一个索引开始
- php - 如何从curl打印值中的文本
- svg - SVG：
标记 以任何 svg 元素为目标 - php - 如何从公共/图像存储符号链接获取路径？
- reactjs - React Native登录后自动更新Async storage Key的数据
- macos - 卡特琳娜的终端“资源暂时不可用”
- javascript - Nodejs Plotlyjs 单独工作，但不能一起工作
- google-cloud-platform - OAuth 同意屏幕 - 删除应用程序徽标的能力