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
    911
    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.
    I Love coding...
    Don't click this link.

  2. #2
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    911
    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
    I Love coding...
    Don't click this link.

  3. #3
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    4,904
    Mentioned
    93 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
    911
    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.
    I Love coding...
    Don't click this link.

  5. #5
    Patience... bronze trophy solidcodes's Avatar
    Join Date
    Jul 2006
    Location
    Philippines
    Posts
    911
    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
    I Love coding...
    Don't click this link.


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
  •