Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    New Coder
    Join Date
    Aug 2013
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts

    MASTERS PLEASE find the error in this sql

    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


    i got error when update,,, pls solve my pbm


    Error: Cannot add or update a child row: a foreign key constraint fails (`student_db`.`subject`, CONSTRAINT `subject_ibfk_1` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`) ON DELETE CASCADE ON UPDATE CASCADE)







    --
    -- 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`
    --

    INSERT INTO `administrator` (`adminid`, `password`, `adminname`, `address`, `contactno`) VALUES
    (123, '83ec45960b80c035a0068df1d9df5aa8', 'ADMIN', '123 password technology', '9876543210');

    -- --------------------------------------------------------

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

  • #2
    New Coder
    Join Date
    Aug 2013
    Posts
    10
    Thanks
    0
    Thanked 1 Time in 1 Post
    'technology' is not exactly a strong admin password...

  • Users who have thanked Artaex for this post:

    seshaa (08-30-2013)

  • #3
    New Coder
    Join Date
    Aug 2013
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by artaex View Post
    'technology' is not exactly a strong admin password...
    thx frd, i will change. Then wats the pbm for this error?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    But you did not bother to show us the query that gives you that error message!

    The message makes perfect sense. You tried to do something that violates the foreign key constraint. But *WHAT* did you do?

    Showing us the schema is nice, but is useless if you don't show us the query that caused the message.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    seshaa (08-31-2013)

  • #5
    New Coder
    Join Date
    Aug 2013
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    But you did not bother to show us the query that gives you that error message!

    The message makes perfect sense. You tried to do something that violates the foreign key constraint. But *WHAT* did you do?

    Showing us the schema is nice, but is useless if you don't show us the query that caused the message.




    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 . wat can i do to solve this problem

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    I'm going to ask ONE MORE TIME and then I will stop trying.

    I created a clone of your database with all the data, directly from what you posted in message #1.

    And then I executed these two queries:
    Code:
    mysql> insert into subject ( subname, courseid, lecid, subtype, semester, comment )
        -> values ( 'JavaScript 101', 5, 0, 'Fundamentals', 2, 'silly' );
    Query OK, 1 row affected (0.01 sec)
    
    mysql> update subject set courseid = 2 where subid = 3;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    And as you can see I was able to both INSERT a record and UPDATE an existing record with no errors.

    So you need to show me a query that DOES NOT WORK. Because I do *NOT* think the problem is in your DB design.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Aug 2013
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    I'm going to ask ONE MORE TIME and then I will stop trying.

    I created a clone of your database with all the data, directly from what you posted in message #1.

    And then I executed these two queries:
    Code:
    mysql> insert into subject ( subname, courseid, lecid, subtype, semester, comment )
        -> values ( 'JavaScript 101', 5, 0, 'Fundamentals', 2, 'silly' );
    Query OK, 1 row affected (0.01 sec)
    
    mysql> update subject set courseid = 2 where subid = 3;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    And as you can see I was able to both INSERT a record and UPDATE an existing record with no errors.

    So you need to show me a query that DOES NOT WORK. Because I do *NOT* think the problem is in your DB design.


    this is my update code..

    if(isset($_POST["button2"]))
    {
    $sql="UPDATE subject SET subname='$_POST[subname]', comment='$_POST[comment]', courseid='$_POST[courseid]', subtype='$_POST[subtype]', semester='$_POST[semester]', lecid='$_POST[lecid]' WHERE subid = '$_POST[subid]'";
    if (!mysql_query($sql,$con))
    {
    die('Error: ' . mysql_error());
    }
    else
    {
    echo "Record updated Successfully...";
    }
    }

  • #8
    New Coder
    Join Date
    Aug 2013
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    if i changed a subject name means it is not changed,

  • #9
    New Coder
    Join Date
    Aug 2013
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by seshaa View Post
    if i changed a subject name means it is not changed,
    when i try to update a subname, or course it shows the following error.


    Error: Cannot add or update a child row: a foreign key constraint fails (`student_db`.`subject`, CONSTRAINT `subject_ibfk_1` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`) ON DELETE CASCADE ON UPDATE CASCADE)

  • #10
    New Coder
    Join Date
    Aug 2013
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    But you did not bother to show us the query that gives you that error message!

    The message makes perfect sense. You tried to do something that violates the foreign key constraint. But *WHAT* did you do?

    Showing us the schema is nice, but is useless if you don't show us the query that caused the message.


    this is the my project

  • #11
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Quote Originally Posted by seshaa View Post
    this is my update code..

    if(isset($_POST["button2"]))
    {
    $sql="UPDATE subject SET subname='$_POST[subname]', comment='$_POST[comment]', courseid='$_POST[courseid]', subtype='$_POST[subtype]', semester='$_POST[semester]', lecid='$_POST[lecid]' WHERE subid = '$_POST[subid]'";
    if (!mysql_query($sql,$con))
    {
    die('Error: ' . mysql_error());
    }
    else
    {
    echo "Record updated Successfully...";
    }
    }
    This tells us NOTHING. How do we know what values are in all those $_POST['xxx'] expressions??

    You must learn to *DEBUG DEBUG DEBUG*.

    AT A MINIMUM, you should do:
    Code:
    $sql="UPDATE subject SET subname='$_POST[subname]',
     	comment='$_POST[comment]', 	courseid='$_POST[courseid]',
     	subtype='$_POST[subtype]', 	semester='$_POST[semester]',
     	lecid='$_POST[lecid]' WHERE subid = '$_POST[subid]'";
    
    echo "DEBUG SQL: " . $sql . "<hr/>\n";
    
    if (!mysql_query($sql,$con))
    ....
    And then inspect the DEBUG output for errors. If you can't see them yourself, then copy paste the DEBUG output here for us to see.

    I see many many other minor errors in that code, not the least of which is that you are using mysql_query which is now officially deprecated.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #12
    New Coder
    Join Date
    Aug 2013
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    This tells us NOTHING. How do we know what values are in all those $_POST['xxx'] expressions??

    You must learn to *DEBUG DEBUG DEBUG*.

    AT A MINIMUM, you should do:
    Code:
    $sql="UPDATE subject SET subname='$_POST[subname]',
     	comment='$_POST[comment]', 	courseid='$_POST[courseid]',
     	subtype='$_POST[subtype]', 	semester='$_POST[semester]',
     	lecid='$_POST[lecid]' WHERE subid = '$_POST[subid]'";
    
    echo "DEBUG SQL: " . $sql . "<hr/>\n";
    
    if (!mysql_query($sql,$con))
    ....
    And then inspect the DEBUG output for errors. If you can't see them yourself, then copy paste the DEBUG output here for us to see.

    I see many many other minor errors in that code, not the least of which is that you are using mysql_query which is now officially deprecated.
    when i try to update a subname, or course it shows the following error.
    Error: Cannot add or update a child row: a foreign key constraint fails (`student_db`.`subject`, CONSTRAINT `subject_ibfk_1` FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`) ON DELETE CASCADE ON UPDATE CASCADE)


    Here i attached my subjectxx.php file in text format
    Attached Files Attached Files

  • #13
    New Coder
    Join Date
    Aug 2013
    Posts
    11
    Thanks
    3
    Thanked 0 Times in 0 Posts
    DEBUG SQL: UPDATE subject SET subname='Englishh', comment='fhjfbg', courseid='', subtype='Language', semester='1', lecid='' WHERE subid = '1'

  • #14
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    BINGO!

    Look right there!

    Code:
    UPDATE subject SET subname='Englishh', comment='fhjfbg', courseid='', subtype='Language', semester='1', lecid='' WHERE subid = '1'
    Just *EXACTLY* what the error message told you:
    foreign key constraint fails
    (`student_db`.`subject`, CONSTRAINT `subject_ibfk_1`
    FOREIGN KEY (`courseid`) REFERENCES `course` (`courseid`)
    The value for courseid in the subject table *MUST MATCH* one of the values of courseid in the course table.

    The only valid values for courseid in the course table are:
    Code:
    mysql> select * from course;
    +----------+----------------------------------+------------------------------------------------+-----------+
    | courseid | coursename                       | comment                                        | coursekey |
    +----------+----------------------------------+------------------------------------------------+-----------+
    |        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       |
    +----------+----------------------------------+------------------------------------------------+-----------+
    I might also point out that your fields subid, courseid, and lecid in your subject table are all defined as BIGINT fields, and so you MUST put *NUMBERS* into them. Meaning that you should *NOT* HAVE APOSTROPHES in the places shown here:
    Code:
    UPDATE subject SET subname='Englishh', comment='fhjfbg', courseid='', subtype='Language', semester='1', lecid='' WHERE subid = '1'
    Also, number fields can *NOT* be blank! You MUST supply either a number for courseid, lecid, and subid or use the keyword NULL (without parentheses). *EXAMPLE*:
    Code:
    UPDATE subject SET subname='Englishh', comment='fhjfbg', courseid=1, subtype='Language', semester='1', lecid=1 WHERE subid = 1
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    seshaa (09-01-2013)

  • #15
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    You see how much simpler this could have been if you had simply done what I asked your for in my post #4? If you had shown me the query that had the problem FIRST you would have had your answer ALMOST TWO DAYS AGO.

    When posting questions in forums, don't make us DRAG the details out of you! Give *ALL* the relevant information in your FIRST post.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •