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