hi admin, i have a error in this sql. i can add a subject in the subject table., bt agian i cant update a existing details in subject table. Its only there in previous data, likewise attendance, student details, examination all tables having same error. pls solve frds,.......... this is my college project... help meeeeeeeeee alllllllll
PHP Code:
--
-- Database: `studentinfo`
--
-- --------------------------------------------------------
--
-- Table structure for table `administrator`
--
CREATE TABLE IF NOT EXISTS `administrator` (
`adminid` bigint(4) NOT NULL AUTO_INCREMENT,
`password` varchar(50) NOT NULL,
`adminname` varchar(80) NOT NULL,
`address` text NOT NULL,
`contactno` varchar(25) NOT NULL,
PRIMARY KEY (`adminid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=124 ;
--
-- Dumping data for table `administrator`
--
-- --------------------------------------------------------
--
-- Table structure for table `attendance`
--
CREATE TABLE IF NOT EXISTS `attendance` (
`attid` bigint(4) NOT NULL AUTO_INCREMENT,
`studid` varchar(20) NOT NULL,
`subid` bigint(4) NOT NULL,
`totalclasses` int(2) NOT NULL,
`attendedclasses` int(2) NOT NULL,
`percentage` double(4,2) NOT NULL,
`comment` text NOT NULL,
PRIMARY KEY (`attid`),
KEY `studid` (`studid`),
KEY `subid` (`subid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `attendance`
--
INSERT INTO `attendance` (`attid`, `studid`, `subid`, `totalclasses`, `attendedclasses`, `percentage`, `comment`) VALUES
(6, '1', 1, 12, 12, 99.99, 'sfsdf');
-- --------------------------------------------------------
--
-- Table structure for table `contact`
--
CREATE TABLE IF NOT EXISTS `contact` (
`contactid` bigint(4) NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
`emailid` varchar(30) NOT NULL,
`contactno` varchar(20) NOT NULL,
`subject` varchar(20) NOT NULL,
`message` text NOT NULL,
PRIMARY KEY (`contactid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
-- --------------------------------------------------------
--
-- Table structure for table `course`
--
CREATE TABLE IF NOT EXISTS `course` (
`courseid` bigint(4) NOT NULL AUTO_INCREMENT,
`coursename` varchar(40) NOT NULL,
`comment` text NOT NULL,
`coursekey` varchar(15) NOT NULL,
PRIMARY KEY (`courseid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `course`
--
INSERT INTO `course` (`courseid`, `coursename`, `comment`, `coursekey`) VALUES
(1, 'Bachelor of Arts', 'This Course is related to Arts field.', 'BA'),
(2, 'Bachelor of Commerce', 'This course is related to commerce field.', 'BCom'),
(3, 'Bachelor of Bussiness Management', 'This course is related to Bussiness field.', 'BBM'),
(4, 'Bachelor of Science', 'This field is related to science field.', 'BSc'),
(5, 'Bachelor of Computer Application', 'This field is related to computer field.', 'BCA'),
(6, 'Bachelor of Social Work', 'This field is related to social welfare field.', 'BSW');
-- --------------------------------------------------------
--
-- Table structure for table `examination`
--
CREATE TABLE IF NOT EXISTS `examination` (
`examid` bigint(4) NOT NULL AUTO_INCREMENT,
`studid` varchar(20) NOT NULL,
`subid` bigint(4) NOT NULL,
`courseid` bigint(4) NOT NULL,
`internaltype` varchar(20) NOT NULL,
`maxmarks` int(2) NOT NULL,
`scored` int(2) NOT NULL,
`percentage` float NOT NULL,
`result` text NOT NULL,
PRIMARY KEY (`examid`),
KEY `subid` (`subid`),
KEY `studid` (`studid`),
KEY `courseid` (`courseid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
-- --------------------------------------------------------
--
-- Table structure for table `lectures`
--
CREATE TABLE IF NOT EXISTS `lectures` (
`lecid` bigint(4) NOT NULL AUTO_INCREMENT,
`password` varchar(50) NOT NULL,
`courseid` bigint(4) NOT NULL,
`lecname` varchar(50) NOT NULL,
`gender` varchar(50) NOT NULL,
`address` varchar(100) NOT NULL,
`contactno` varchar(15) NOT NULL,
PRIMARY KEY (`lecid`),
KEY `courseid` (`courseid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
--
-- Dumping data for table `lectures`
--
INSERT INTO `lectures` (`lecid`, `password`, `courseid`, `lecname`, `gender`, `address`, `contactno`) VALUES
(1, '0cc175b9c0f1b6a831c399e269772661', 5, 'geetha', 'Female', 'fgv', '9876543211');
-- --------------------------------------------------------
--
-- Table structure for table `semester`
--
CREATE TABLE IF NOT EXISTS `semester` (
`semid` bigint(4) NOT NULL AUTO_INCREMENT,
`semester` varchar(25) NOT NULL,
`comment` text NOT NULL,
PRIMARY KEY (`semid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
-- --------------------------------------------------------
--
-- Table structure for table `studentdetails`
--
CREATE TABLE IF NOT EXISTS `studentdetails` (
`studid` varchar(25) NOT NULL,
`studfname` varchar(20) NOT NULL,
`studlname` varchar(20) NOT NULL,
`fathername` varchar(25) NOT NULL,
`gender` varchar(20) NOT NULL,
`address` varchar(100) NOT NULL,
`contactno` varchar(20) NOT NULL,
`courseid` bigint(4) NOT NULL,
`semester` varchar(20) NOT NULL,
`dob` date NOT NULL,
PRIMARY KEY (`studid`),
KEY `courseid` (`courseid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `studentdetails`
--
INSERT INTO `studentdetails` (`studid`, `studfname`, `studlname`, `fathername`, `gender`, `address`, `contactno`, `courseid`, `semester`, `dob`) VALUES
('1', 'sam', 'a', 'asdf', 'Male', 'xcv', '452757855', 5, '1', '1996-02-09');
-- --------------------------------------------------------
--
-- Table structure for table `subject`
--
CREATE TABLE IF NOT EXISTS `subject` (
`subid` bigint(4) NOT NULL AUTO_INCREMENT,
`subname` varchar(20) NOT NULL,
`courseid` bigint(4) NOT NULL,
`lecid` bigint(4) NOT NULL,
`subtype` varchar(25) NOT NULL,
`semester` varchar(25) NOT NULL,
`comment` text NOT NULL,
PRIMARY KEY (`subid`),
KEY `courseid` (`courseid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
--
-- Dumping data for table `subject`
--
INSERT INTO `subject` (`subid`, `subname`, `courseid`, `lecid`, `subtype`, `semester`, `comment`) VALUES
(1, 'English', 1, 0, 'Language', '1', 'fhjfbg'),
(3, 'Accounting', 3, 0, 'Theory', '1', 'jsjk');
--
-- Constraints for dumped tables
--
--
-- Constraints for table `attendance`
--
ALTER TABLE `attendance`
ADD CONSTRAINT `attendance_ibfk_1` FOREIGN KEY (`subid`) REFERENCES `subject` (`subid`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `attendance_ibfk_2` FOREIGN KEY (`studid`) REFERENCES `studentdetails` (`studid`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `examination`
--
ALTER TABLE `examination`
ADD CONSTRAINT `examination_ibfk_1` FOREIGN KEY (`studid`) REFERENCES `studentdetails` (`studid`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `examination_ibfk_2` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `examination_ibfk_3` FOREIGN KEY (`subid`) REFERENCES `subject` (`subid`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `lectures`
--
ALTER TABLE `lectures`
ADD CONSTRAINT `lectures_ibfk_1` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `studentdetails`
--
ALTER TABLE `studentdetails`
ADD CONSTRAINT `studentdetails_ibfk_1` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`) ON DELETE CASCADE ON UPDATE CASCADE;
--
-- Constraints for table `subject`
--
ALTER TABLE `subject`
ADD CONSTRAINT `subject_ibfk_1` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`) ON DELETE CASCADE ON UPDATE CASCADE;
Comment