最近期末了,老师让做一个课程设计,所以大家就开始百度了(懂的都懂)。同学找了一个项目,但是数据库的建立用作者 上传的代码一直创建不成功,我把代码要了过来尝试着运行起来,写下这篇博客记录遇到的问题。
首先呈上原数据库的代码:
CREATE TABLE `admitpatient_room` (
`PatientID` int(10) NOT NULL,
`Disease` varchar(50) DEFAULT NULL,
`AdmitDate` date DEFAULT NULL,
`RoomNo` int(5) DEFAULT NULL,
`DoctorID` int(10) NOT NULL,
`AP_Remarks` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PatientID`),
KEY `RoomNo` (`RoomNo`),
KEY `DoctorID` (`DoctorID`),
CONSTRAINT `admitpatient_room_ibfk_1` FOREIGN KEY (`RoomNo`) REFERENCES `room` (`RoomNo`),
CONSTRAINT `admitpatient_room_ibfk_2` FOREIGN KEY (`DoctorID`) REFERENCES `doctor` (`DoctorID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `bill_room` (
`DischargelID` int(5) NOT NULL AUTO_INCREMENT,
`BillNo` int(15) NOT NULL,
`BillingDate` date DEFAULT NULL,
`RoomCharges` int(10) DEFAULT NULL,
`ServiceCharges` int(10) DEFAULT NULL,
`PaymentMode` varchar(20) DEFAULT NULL,
`PaymentModeDetails` varchar(100) DEFAULT NULL,
`TotalCharges` int(10) DEFAULT NULL,
`NoOfDays` int(5) DEFAULT NULL,
`TotalRoomCharges` int(10) DEFAULT NULL,
PRIMARY KEY (`DischargeID`,`BillNo`),
UNIQUE KEY `BillNo_UNIQUE` (`BillNo`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
CREATE TABLE `dischargepatient_room` (
`AdmitID` int(20) NOT NULL AUTO_INCREMENT,
`DischargeDate` date DEFAULT NULL,
`DP_Remarks` varchar(50) DEFAULT NULL,
PRIMARY KEY (`AdmitID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
CREATE TABLE `doctor` (
`DoctorID` int(10) NOT NULL AUTO_INCREMENT,
`DoctorName` varchar(20) DEFAULT NULL,
`FatherName` varchar(20) DEFAULT NULL,
`Email` varchar(50) DEFAULT NULL,
`ContacNo` varchar(11) DEFAULT NULL,
`Qualifications` varchar(50) DEFAULT NULL,
`Gender` varchar(1) DEFAULT NULL,
`BloodGroup` varchar(5) DEFAULT NULL,
`DateOfJoining` date DEFAULT NULL,
`Address` varchar(100) DEFAULT NULL,
PRIMARY KEY (`DoctorID`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=latin1
CREATE TABLE `patientregistration` (
`PatientID` int(10) NOT NULL AUTO_INCREMENT,
`PatientName` varchar(20) DEFAULT NULL,
`Email` varchar(30) DEFAULT NULL,
`ContactNo` varchar(11) DEFAULT NULL,
`Age` int(2) DEFAULT NULL,
`Remarks` varchar(100) DEFAULT NULL,
`Gen` varchar(1) DEFAULT NULL,
`BG` varchar(3) DEFAULT NULL,
`Address` varchar(100) DEFAULT NULL,
PRIMARY KEY (`PatientID`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
CREATE TABLE `registration` (
`name` varchar(20) DEFAULT NULL,
`user_name` varchar(20) NOT NULL,
`password` varchar(20) DEFAULT NULL,
`email_id` varchar(30) DEFAULT NULL,
`contact_no` int(11) DEFAULT NULL,
PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `room` (
`RoomNo` int(5) NOT NULL,
`RoomType` varchar(10) DEFAULT NULL,
`RoomCharges` int(10) DEFAULT NULL,
`RoomStatus` varchar(10) DEFAULT NULL,
PRIMARY KEY (`RoomNo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `services` (
`ServiceName` varchar(20) DEFAULT NULL,
`ServiceDate` date DEFAULT NULL,
`PatientID` int(10) DEFAULT NULL,
`ServiceCharges` int(10) DEFAULT NULL,
`ServiceID` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ServiceID`),
KEY `PatientID` (`PatientID`),
CONSTRAINT `services_ibfk_1` FOREIGN KEY (`PatientID`) REFERENCES `patientregistration` (`PatientID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
CREATE TABLE `users` (
`user_name` varchar(20) NOT NULL,
`password` varchar(20) DEFAULT NULL,
PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
首先打一套Ctrl + a/c/v 的组合拳将代码下载到桌面,并保存为 .sql文件;
然后用Navicat Premium 运行sql文件。恩……不出所料,报了一处错误。但不知道是哪错了,也没有提示。所以我只好再把代码在数据库的控制板下运行。这下有了报错的信息:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘CREATE TABLE bill_room
( DischargelID
int(5) NOT NULL AUTO_INCREMENT,
`Bi’ at line 15
emm……,第15行有问题,找到第15行:
CREATE TABLE `bill_room` (
`DischargelID` int(5) NOT NULL AUTO_INCREMENT,
`BillNo` int(15) NOT NULL,
`BillingDate` date DEFAULT NULL,
`RoomCharges` int(10) DEFAULT NULL,
`ServiceCharges` int(10) DEFAULT NULL,
`PaymentMode` varchar(20) DEFAULT NULL,
`PaymentModeDetails` varchar(100) DEFAULT NULL,
`TotalCharges` int(10) DEFAULT NULL,
`NoOfDays` int(5) DEFAULT NULL,
`TotalRoomCharges` int(10) DEFAULT NULL,
PRIMARY KEY (`DischargeID`,`BillNo`),
UNIQUE KEY `BillNo_UNIQUE` (`BillNo`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
大概就是这一块出现了问题,查看代码后发现是主键的问题,所以改他的代码:
CREATE TABLE `bill_room` (
`DischargelID` int(5) PRIMARY key,
`BillNo` int(15) NOT NULL,
`BillingDate` date DEFAULT NULL,
`RoomCharges` int(10) DEFAULT NULL,
`ServiceCharges` int(10) DEFAULT NULL,
`PaymentMode` varchar(20) DEFAULT NULL,
`PaymentModeDetails` varchar(100) DEFAULT NULL,
`TotalCharges` int(10) DEFAULT NULL,
`NoOfDays` int(5) DEFAULT NULL,
`TotalRoomCharges` int(10) DEFAULT NULL,
UNIQUE KEY `BillNo_UNIQUE` (`BillNo`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
然后单独运行这一块的:
创建成功,就当我认为代码已经没有错误时,事实证明我太天真了。又报错了:
ERROR 1215 (HY000): Cannot add foreign key constraint,
代码如下:
CREATE TABLE `admitpatient_room` (
`PatientID` int(10) NOT NULL,
`Disease` varchar(50) DEFAULT NULL,
`AdmitDate` date DEFAULT NULL,
`RoomNo` int(5) DEFAULT NULL,
`DoctorID` int(10) NOT NULL,
`AP_Remarks` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PatientID`),
KEY `RoomNo` (`RoomNo`),
KEY `DoctorID` (`DoctorID`),
CONSTRAINT `admitpatient_room_ibfk_1` FOREIGN KEY (`RoomNo`) REFERENCES `room` (`RoomNo`),
CONSTRAINT `admitpatient_room_ibfk_2` FOREIGN KEY (`DoctorID`) REFERENCES `doctor` (`DoctorID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
第一块代码出现了问题,外键问题。把外键一删立马就跑起来了。关键不能删除就完事了,毕竟是从网上下载的,万一已删除出现别的问题咋办。然后我就看了下外键的设置,发现是关于room
表和doctor
表的两个外键,于是我就想,没有这两个表,外键能创立吗?然后我就试着先创建这两个表,然后在运行上面这块代码。
嘿嘿,还真让我猜对了,调换顺序后你在执行果然没有报错。至此算是把数据库给创建好了。两个简单的基础语句的错误都能让我半天解决不出来,我太菜了。菜归菜,就这样在错误中慢慢成长吧。
收获:
1、https://blog.csdn.net/weixin_44096353/article/details/111994010