SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    MySQL said: #1452 ?

    Hi guys,

    I'm trying to add a FK on Media Table the parents table are Classes and Lessons tables.
    And when I tried to add a Foreign Keys this is the error messages below.

    First error message:
    Code:
    Error
    
    SQL query:
    
    ALTER TABLE  `media` ADD FOREIGN KEY (  `classid` ) REFERENCES  `myonliq6_warren`.`classes` (
    
    `classid`
    ) ON DELETE CASCADE ON UPDATE CASCADE ;
    
    MySQL said: Documentation
    
    #1452 - Cannot add or update a child row: a foreign key constraint fails (`myonliq6_warren`.`#sql-5dc9_a0db8d`, CONSTRAINT `#sql-5dc9_a0db8d_ibfk_1` FOREIGN KEY (`classid`) REFERENCES `classes` (`classid`) ON DELETE CASCADE ON UPDATE CASCADE)
    Second error message:
    Code:
    Error
    SQL query:
    
    ALTER TABLE  `media` ADD FOREIGN KEY (  `lessonid` ) REFERENCES  `myonliq6_warren`.`lessons` (
    
    `lessonid`
    ) ON DELETE CASCADE ON UPDATE CASCADE ;
    
    MySQL said: Documentation
    
    #1452 - Cannot add or update a child row: a foreign key constraint fails (`myonliq6_warren`.`#sql-5dc9_a0db8d`, CONSTRAINT `#sql-5dc9_a0db8d_ibfk_1` FOREIGN KEY (`lessonid`) REFERENCES `lessons` (`lessonid`) ON DELETE CASCADE ON UPDATE CASCADE)
    What could be the reason for this?

    Thanks in advance.
    Quality codes are optimized and tested...
    Click here for inspiration..

  2. #2
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    These are my tables.

    Code:
    Classes
    -----------------------------
    classid
    nameclass
    image
    description
    pathwayid

    Code:
    Lessons
    -----------------------------
    lessonid
    namelesson
    image
    description
    partid

    Code:
    Media
    -----------------------------
    mediaid
    namelesson
    image
    description
    classid
    lessonid
    Quality codes are optimized and tested...
    Click here for inspiration..

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,068
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you please post the output of a SHOW CREATE TABLE for the three tables?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  4. #4
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    @SpacePhoenix ;

    Code:
    CREATE TABLE `classes` (
     `classid` int(12) NOT NULL AUTO_INCREMENT,
     `nameclass` varchar(100) NOT NULL,
     `image` varchar(250) NOT NULL,
     `description` varchar(250) NOT NULL,
     `pathwayid` int(12) DEFAULT NULL,
     PRIMARY KEY (`classid`),
     KEY `name` (`nameclass`,`pathwayid`),
     KEY `name_2` (`nameclass`,`pathwayid`),
     KEY `pathwayid` (`pathwayid`),
     CONSTRAINT `classes_ibfk_1` FOREIGN KEY (`pathwayid`) REFERENCES `pathways` (`pathwayid`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1

    Code:
    CREATE TABLE `lessons` (
     `lessonid` int(12) NOT NULL AUTO_INCREMENT,
     `namelesson` varchar(100) NOT NULL,
     `title` varchar(100) NOT NULL,
     `body` text NOT NULL,
     `question` varchar(250) NOT NULL,
     `essay` varchar(5) NOT NULL,
     `essay_minimum_length` varchar(250) NOT NULL,
     `multiple_choice_opt1` varchar(250) NOT NULL,
     `multiple_choice_opt2` varchar(250) NOT NULL,
     `multiple_choice_opt3` varchar(250) NOT NULL,
     `multiple_choice_opt4` varchar(250) NOT NULL,
     `correct_choice` varchar(15) NOT NULL,
     `answer_1_feedback` varchar(250) NOT NULL,
     `answer_2_feedback` varchar(250) NOT NULL,
     `answer_3_feedback` varchar(250) NOT NULL,
     `answer_4_feedback` varchar(250) NOT NULL,
     `partid` int(12) DEFAULT NULL,
     PRIMARY KEY (`lessonid`),
     KEY `name` (`namelesson`),
     KEY `title` (`title`),
     KEY `partid` (`partid`),
     KEY `partid_2` (`partid`),
     KEY `partid_3` (`partid`),
     CONSTRAINT `lessons_ibfk_1` FOREIGN KEY (`partid`) REFERENCES `parts` (`partid`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1

    Code:
    CREATE TABLE `media` (
     `mediaid` int(12) NOT NULL AUTO_INCREMENT,
     `name` varchar(100) NOT NULL,
     `youtube` varchar(250) NOT NULL,
     `image` varchar(250) NOT NULL,
     `pdf` varchar(250) NOT NULL,
     `mp3` varchar(250) NOT NULL,
     `media_center_video` varchar(250) NOT NULL,
     `classid` int(12) DEFAULT NULL,
     `lessonid` int(12) DEFAULT NULL,
     PRIMARY KEY (`mediaid`),
     KEY `youtube` (`youtube`,`image`,`pdf`,`mp3`,`media_center_video`),
     KEY `name` (`name`),
     KEY `classid` (`classid`),
     KEY `lessonid` (`lessonid`),
     KEY `name_2` (`name`,`classid`,`lessonid`),
     KEY `classid_2` (`classid`),
     KEY `lessonid_2` (`lessonid`),
     KEY `classid_3` (`classid`)
    ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1
    Thanks in advance dude.
    Quality codes are optimized and tested...
    Click here for inspiration..

  5. #5
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    936
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I drop Media table. (Recreated from scratch again)

    So what I learned from this is tables must be created in order.
    Parent must be created first then after the children tables...

    Problem solved!

    by myself again. lol
    Quality codes are optimized and tested...
    Click here for inspiration..


Bookmarks

Posting Permissions

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