mysql - #1215 - 无法添加外键约束 :/-6ren

mysql - #1215 - 无法添加外键约束 :/

转载作者：行者123 更新时间：2023-11-29 07:30:10

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)
);

关于mysql - #1215 - 无法添加外键约束 :/，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/51751644/

文章推荐： php - 将 Sakila 数据库导入 PhpMyAdmin

文章推荐： java - JPA:OneToOne 关系所有者

文章推荐： java - 使用 Java 8 迭代二维数组

文章推荐： java - 我可以阻止从代码中调用 Fragment 的构造函数吗？

Oracle 约束
我可以添加一个检查约束来确保所有值都是唯一的，但允许默认值重复吗？最佳答案您可以使用基于函数的索引 (FBI) 来实现此目的: create unique index idx on my_tabl
grails - 约束
嗨，我在让我的约束在grails项目中工作时遇到了一些麻烦。我试图确保Site_ID的字段不留为空白，但仍接受空白输入。另外，我尝试设置字段显示的顺序，但即使尝试时也无法反射(reflect)在页面上
Postgresql 约束
我似乎做错了，我正在尝试将一个字段修改为外键，并使用级联删除...我做错了什么？ ALTER TABLE my_table ADD CONSTRAINT $4 FOREIGN KEY my_field
Mysql基础7-约束
阅读目录 1、约束的基本概念 2、约束的案例实践 3、外键约束介绍 4、外键约束展示 5、删除
12、SQLite 约束
SQLite 约束约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。约束可以是列级或表级。列级约束仅适用于列，表级约束被应用到整
可变参数模板参数的 C++ 约束
我在 SerenityOS project 中偶然发现了这段代码: template void dbgln(CheckedFormatString&& fmtstr, const Parameters
postgresql - 约束，基于与另一个表的连接
我有表 tariffs，有两列:(tariff_id, reception) 我有表 users，有两列:(user_id, reception) 我的表 users_tariffs 有两列:(use
sql - Derby - 约束
在 Derby 服务器中，如何使用模式的系统表中的信息来创建选择语句以检索每个表的约束名称？最佳答案相关手册是Derby Reference Manual .有许多可用版本:10.13 是 201
Z3 如何检查模型是否满足新的断言/约束
我正在使用 z3py 进行编码。请参阅以下示例。 from z3 import * x = Int('x') y = Int('y') s = Solver() s.add(x+y>3) if s.c
临时表上的 SQL 约束
非常快速和简单的问题。我正在运行一个脚本来导入数据并声明了一个临时表并将检查约束应用于该表。显然，如果脚本运行不止一次，我会检查临时表是否已经存在，如果存在，我会删除并重新创建临时表。这也会删除并重新
constraints - 在线性规划中表达 OR 约束
我有一个浮点变量 x在一个线性程序中，它应该是 0或两个常量之间 CONSTANT_A和 CONSTANT_B : LP.addConstraint(x == 0 OR CONSTANT_A <= x
grails - Spring数据neo4j没有在Grails中创建唯一的索引/约束
我在使用grails的spring-data-neo4j获得唯一约束时遇到了一些麻烦。我怀疑这是因为我没有正确连接它，但是存储库正在扫描和连接，并且CRUD正在工作，所以我不确定我做错了什么。我正
c# - 约束、通用变量和算术运算符
这个问题在这里已经有了答案: Is there a constraint that restricts my generic method to numeric types? (24 个回答) 7年前
constraints - 在线性规划中表达 OR 约束
我有一个浮点变量 x在一个线性程序中，它应该是 0或两个常量之间 CONSTANT_A和 CONSTANT_B : LP.addConstraint(x == 0 OR CONSTANT_A <= x
iOS 约束 - 中心图像和动态文本
在iOS的 ScrollView 中将图像和带有动态文本(动态高度)的标签居中的最佳方法是什么？我必须添加哪些约束？我真的无法弄清楚它是如何工作的，也许我无法处理它，因为我是一名 Android 开
haskell - 部分应用类型与种类 (* -> 约束)
考虑以下代码: class Foo f class Bar b newtype D d = D call :: Proxy c -> (forall a . c a => a -> Bool) ->
haskell - 如何避免必须显式编写复合KnownNat 约束？
我有一个类型类，它强加了 KnownNat约束: class KnownNat (Card a) => HasFin a where type Card a :: Nat ... 而且，我有几
rest - 使用基于REST的API不能仅使用基于HTTP的API可以实现哪些功能/约束
我知道REST原则上与HTTP无关。 HTTP是协议，REST是用于通过Web传输hypermedia的体系结构样式。 REST可以使用诸如HTTP，FTP等的任何应用程序层协议。关于REST的讨论很
计数列的 MySQL 约束
我有这样的情况，我必须在数据库中存储复杂的数据编号。类似于 21/2011，其中 21 是文件编号，但 2011 是文件年份。所以我需要一些约束来处理唯一性，因为有编号为 21/2010 和 21/2
MySQL 约束 - 一组中的一列必须是非空的？
我有一个 MySql (InnoDb) 表，表示对许多类型的对象之一所做的评论。因为我正在使用 Concrete Table Inheritance ，对于下面显示的每种类型的对象(商店、类别、项目)

行者123

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城

mysql - #1215 - 无法添加外键约束 :/