您的位置: 首页  >  技术问答  >  错误代码1005无法创建表

错误代码1005无法创建表

分类: 技术问答 • 2022-07-23 14:57:31
问题描述：

嗨
我已经创建了一个用于创建表的存储过程，当我执行它时，它就会被执行，当我调用该存储过程时，会出现错误

错误代码1005:无法创建表

这是我的存储过程，请纠正我哪里出错了

Hi
I have created a stored procedure for creating tables and when i execute it it is getting executed and when i call the stored procedure i get the error

error code 1005:cannot create the table

this is my stored procedure pl correct me where i went wrong

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_CreateTables`()
BEGIN
CREATE TABLE `tblassociatedetails` (
  `AssociateId` int(11) NOT NULL ,
  `AssociatePwd` varchar(30) NOT NULL,
  `AssociateFName` varchar(30) NOT NULL,
  `AssociateMName` varchar(30) NOT NULL,
  `AssociateLName` varchar(30) NOT NULL,
  `ManagerId` int(11) NOT NULL,
  `DesigId` int(11) NOT NULL,
  `DeptId` int(11) NOT NULL,
  `UserRoleId` int(11) NOT NULL,
  `SecQuestID` int(11) DEFAULT NULL,
  `Sex` varchar(6) NOT NULL,
  `FatherName` varchar(30) NOT NULL,
  `MotherName` varchar(30) NOT NULL,
  `CurrentAdd` varchar(1000) NOT NULL,
  `PermanentAdd` varchar(1000) NOT NULL,
  `EmergContactAdd` varchar(1000) NOT NULL,
  `EmergContactNo` varchar(20) NOT NULL,
  `SecAnswer` varchar(40) NOT NULL,
   KEY `DeptId` (`DeptId`),
  KEY `DesigId` (`DesigId`),
  KEY `UserRoleId` (`UserRoleId`),
  CONSTRAINT `DeptId` FOREIGN KEY (`DeptId`) REFERENCES `tbldepartment` (`DeptId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `DesigId` FOREIGN KEY (`DesigId`) REFERENCES `tbldesignation` (`DesigId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `UserRoleId` FOREIGN KEY (`UserRoleId`) REFERENCES `tbluserrole` (`UserRoleId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ;
CREATE TABLE `tbldepartment` (
  `DeptId` int(11) NOT NULL AUTO_INCREMENT,
  `DeptName` varchar(20) NOT NULL,
  `Description` varchar(30) NOT NULL,
   PRIMARY KEY (`DeptId`)
);
CREATE TABLE `tbldesignation` (
  `DesigId` int(11) NOT NULL AUTO_INCREMENT,
  `DesigName` varchar(20) NOT NULL,
  `Description` varchar(30) NOT NULL,
   PRIMARY KEY (`DesigId`)
);
CREATE TABLE `tblleaverequest` (
  `LeaveReqId` int(11) NOT NULL AUTO_INCREMENT,
  `AssociateId` int(11) NOT NULL,
  `ManagerId` int(11) NOT NULL,
  `LeaveTypeId` int(11) NOT NULL,
  `LeaveStatusId` int(11) NOT NULL,
  `NoOfDays` float NOT NULL,
  `From` datetime NOT NULL,
  `To` datetime NOT NULL,
  `AddressOnLeave` varchar(1000) NOT NULL,
   PRIMARY KEY (`LeaveReqId`),
  KEY `LeaveTypeId` (`LeaveTypeId`),
  KEY `LeaveStatusId` (`LeaveStatusId`),
  CONSTRAINT `LeaveStatusId` FOREIGN KEY (`LeaveStatusId`) REFERENCES `tblleavestatus` (`LeaveStatusId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `LeaveTypeId` FOREIGN KEY (`LeaveTypeId`) REFERENCES `tblleavetype` (`LeaveTypeId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ;
CREATE TABLE `tblleavestatus` (
  `LeaveStatusId` int(11) NOT NULL AUTO_INCREMENT,
  `LeaveStatus` varchar(15) NOT NULL,
   PRIMARY KEY (`LeaveStatusId`)
) ;
CREATE TABLE `tblleavetype` (
  `LeaveTypeId` int(11) NOT NULL AUTO_INCREMENT,
  `LeaveType` varchar(45) NOT NULL,
   PRIMARY KEY (`LeaveTypeId`)
) ;
CREATE TABLE `tblloginhistory` (
  `LoginHistId` int(11) NOT NULL AUTO_INCREMENT,
  `AssociateId` int(11) NOT NULL,
  `LoggedIn` datetime NOT NULL,
  `LoggedOut` datetime NOT NULL,
  PRIMARY KEY (`LoginHistId`)
) ;
CREATE TABLE `tblsecretquestion` (
  `SecQuestID` int(11) DEFAULT NULL,
  `SecQuestion` varchar(100) DEFAULT NULL
);
CREATE TABLE `tbluserrole` (
  `UserRoleId` int(11) NOT NULL AUTO_INCREMENT,
  `UserRole` varchar(15) NOT NULL,
   PRIMARY KEY (`UserRoleId`)
);
END

答

创建 DEFINER =`root` @`localhost` 过程`sp_CreateTables`() 开始 创建 表`tblassociatedetails`( `AssociateId` int ( 11 )不 NULL ， `AssociatePwd` varchar ( 30 )不 NULL ， `AssociateFName` varchar ( 30 )>不是 NULL ， `AssociateMName` varchar ( 30 )>不是 NULL ， `AssociateLName` varchar ( 30 )>不是 NULL ， `ManagerId` int ( 11 )不 NULL ， `DesigId` int ( 11 )不 NULL ， `DeptId` int ( 11 )>不 NULL ， `UserRoleId` int ( 11 )不 NULL ， `SecQuestID` int ( 11 ) DEFAULT NULL ， `Sex` varchar ( 6 )不 NULL ， `FatherName` varchar ( 30 )>不 NULL ， 母亲名称 varchar ( 30 )不 NULL ， `CurrentAdd` varchar ( 1000 )>不 NULL ， `PermanentAdd` varchar ( 1000 )不 NULL ， `EmergContactAdd` varchar ( 1000 )>不 NULL ， `EmergContactNo` varchar ( 20 )>不 NULL ， `SecAnswer` varchar ( 40 )>不 NULL ， KEY `DeptId`(`DeptId`)， KEY `DesigId`(`DesigId`)， KEY `UserRoleId`(`UserRoleId`)， CONSTRAINT `DeptId` FOREIGN KEY (` DeptId`)参考`tbldepartment`(`DeptId`) ON 删除不采取行动打开 更新不采取行动， CONSTRAINT `DesigId` FOREIGN KEY (` DesigId`)参考`tbldesignation`(`DesigId`)打开 删除不采取行动打开 更新不采取行动， CONSTRAINT `UserRoleId` FOREIGN KEY (` UserRoleId`)参考`tbluserrole`(`UserRoleId`)打开 删除不采取行动打开 更新不采取行动 ); 创建 TABLE `tbldepartment`( `DeptId` int ( 11 )>不 NULL AUTO_INCREMENT， `DeptName` varchar ( 20 )不 NULL ， `描述` varchar ( 30 )>不 NULL ， 主要 KEY (`DeptId`) ); 创建 表`tbldesignation`( `DesigId` int ( 11 )不 NULL AUTO_INCREMENT， DesigName` varchar ( 20 )不 NULL ， `描述` varchar ( 30 )>不 NULL ， 主要 KEY (`DesigId`) ); 创建 表`tblleaverequest`( `LeaveReqId` int ( 11 )不 NULL AUTO_INCREMENT， `AssociateId` int ( 11 )不 NULL ， `ManagerId` int ( 11 )不 NULL ， `LeaveTypeId` int ( 11 )不 NULL ， `LeaveStatusId` int ( 11 )不 NULL ， `NoOfDays` float NOT NULL ， `From` 日期时间 不 NULL ， To To datetime NOT NULL ， `AddressOnLeave` varchar ( 1000 )不 NULL ， PRIMARY KEY (`LeaveReqId`)， KEY `LeaveTypeId`(`LeaveTypeId`)， KEY `LeaveStatusId`(`LeaveStatusId`)， CONSTRAINT `LeaveStatusId` FOREIGN KEY (` LeaveStatusId`)参考`tblleavestatus`(`LeaveStatusId`)打开 删除不采取行动打开 更新不采取行动， CONSTRAINT `LeaveTypeId` FOREIGN KEY (` LeaveTypeId`)参考`tblleavetype`(`LeaveTypeId`)打开 删除不采取行动打开 更新不采取行动 ); 创建 表`tblleavestatus`( `LeaveStatusId` int ( 11 )不 NULL AUTO_INCREMENT， `LeaveStatus` varchar ( 15 )不 NULL ， 主要 KEY (`LeaveStatusId`) ); 创建 表`tblleavetype`( `LeaveTypeId` int ( 11 )不 NULL AUTO_INCREMENT， `LeaveType` varchar ( 45 )不 NULL ， 主要 KEY (`LeaveTypeId`) ); 创建 表`tblloginhistory`( `LoginHistId` int ( 11 )不 NULL AUTO_INCREMENT， `AssociateId` int ( 11 )不 NULL ， `LoggedIn` 日期时间 不 NULL ， `LoggedOut` 日期时间 不 NULL ， 主要 KEY (`LoginHistId`) ); 创建 表`tblsecretquestion`( `SecQuestID` int ( 11 ) DEFAULT NULL ， `SecQuestion` varchar ( 100 )>默认 NULL ); 创建 表`tbluserrole`( `UserRoleId` int ( 11 )不 NULL AUTO_INCREMENT， `UserRole` varchar ( 15 )不 NULL ， 主要 KEY (`UserRoleId`) ); END
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_CreateTables`() BEGIN CREATE TABLE `tblassociatedetails` ( `AssociateId` int(11) NOT NULL , `AssociatePwd` varchar(30) NOT NULL, `AssociateFName` varchar(30) NOT NULL, `AssociateMName` varchar(30) NOT NULL, `AssociateLName` varchar(30) NOT NULL, `ManagerId` int(11) NOT NULL, `DesigId` int(11) NOT NULL, `DeptId` int(11) NOT NULL, `UserRoleId` int(11) NOT NULL, `SecQuestID` int(11) DEFAULT NULL, `Sex` varchar(6) NOT NULL, `FatherName` varchar(30) NOT NULL, `MotherName` varchar(30) NOT NULL, `CurrentAdd` varchar(1000) NOT NULL, `PermanentAdd` varchar(1000) NOT NULL, `EmergContactAdd` varchar(1000) NOT NULL, `EmergContactNo` varchar(20) NOT NULL, `SecAnswer` varchar(40) NOT NULL, KEY `DeptId` (`DeptId`), KEY `DesigId` (`DesigId`), KEY `UserRoleId` (`UserRoleId`), CONSTRAINT `DeptId` FOREIGN KEY (`DeptId`) REFERENCES `tbldepartment` (`DeptId`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `DesigId` FOREIGN KEY (`DesigId`) REFERENCES `tbldesignation` (`DesigId`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `UserRoleId` FOREIGN KEY (`UserRoleId`) REFERENCES `tbluserrole` (`UserRoleId`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ; CREATE TABLE `tbldepartment` ( `DeptId` int(11) NOT NULL AUTO_INCREMENT, `DeptName` varchar(20) NOT NULL, `Description` varchar(30) NOT NULL, PRIMARY KEY (`DeptId`) ); CREATE TABLE `tbldesignation` ( `DesigId` int(11) NOT NULL AUTO_INCREMENT, `DesigName` varchar(20) NOT NULL, `Description` varchar(30) NOT NULL, PRIMARY KEY (`DesigId`) ); CREATE TABLE `tblleaverequest` ( `LeaveReqId` int(11) NOT NULL AUTO_INCREMENT, `AssociateId` int(11) NOT NULL, `ManagerId` int(11) NOT NULL, `LeaveTypeId` int(11) NOT NULL, `LeaveStatusId` int(11) NOT NULL, `NoOfDays` float NOT NULL, `From` datetime NOT NULL, `To` datetime NOT NULL, `AddressOnLeave` varchar(1000) NOT NULL, PRIMARY KEY (`LeaveReqId`), KEY `LeaveTypeId` (`LeaveTypeId`), KEY `LeaveStatusId` (`LeaveStatusId`), CONSTRAINT `LeaveStatusId` FOREIGN KEY (`LeaveStatusId`) REFERENCES `tblleavestatus` (`LeaveStatusId`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `LeaveTypeId` FOREIGN KEY (`LeaveTypeId`) REFERENCES `tblleavetype` (`LeaveTypeId`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ; CREATE TABLE `tblleavestatus` ( `LeaveStatusId` int(11) NOT NULL AUTO_INCREMENT, `LeaveStatus` varchar(15) NOT NULL, PRIMARY KEY (`LeaveStatusId`) ) ; CREATE TABLE `tblleavetype` ( `LeaveTypeId` int(11) NOT NULL AUTO_INCREMENT, `LeaveType` varchar(45) NOT NULL, PRIMARY KEY (`LeaveTypeId`) ) ; CREATE TABLE `tblloginhistory` ( `LoginHistId` int(11) NOT NULL AUTO_INCREMENT, `AssociateId` int(11) NOT NULL, `LoggedIn` datetime NOT NULL, `LoggedOut` datetime NOT NULL, PRIMARY KEY (`LoginHistId`) ) ; CREATE TABLE `tblsecretquestion` ( `SecQuestID` int(11) DEFAULT NULL, `SecQuestion` varchar(100) DEFAULT NULL ); CREATE TABLE `tbluserrole` ( `UserRoleId` int(11) NOT NULL AUTO_INCREMENT, `UserRole` varchar(15) NOT NULL, PRIMARY KEY (`UserRoleId`) ); END


尝试添加注释；创建表1到1，这样您就可以知道创建时哪个表给您带来了问题，然后，如果无法解决问题，请给出该代码.
try commenting & creating tables 1 by 1, so that you will know that which table is giving you problem while creation , then if unable to solve problem , give that code.


相关推荐