SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)

    "cannot add or update a child row: a foreign key"

    Hi Guys

    I have two tables, one called 'meetme' and the other called 'frontpage_conferences'.

    I have a dual primary key on the meetme table which consists of 'confno' and 'starttime'.

    I have a foreign key inside frontpage_conferences which links to the primary key in meetme.

    Whenever I try to add to the frontpage_conferences table I get this error:


    Cannot add or update a child row: a foreign key constraint fails (`03talk`.`frontpage_conferences`, CONSTRAINT `fk_frontpage_conferences_meetme1` FOREIGN KEY (`meetme_confno`, `meetme_starttime`) REFERENCES `meetme` (`confno`, `starttime`) ON DELETE NO ACTIO)


    It's driving me mad.

    Here is my table structure sql:

    Code MySQL:
    SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
    SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
    SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
     
     
    CREATE  TABLE IF NOT EXISTS `meetme` (
      `confno` CHAR(80) NOT NULL DEFAULT '0' ,
      `starttime` DATETIME NOT NULL DEFAULT '2001-01-01 00:00:00' ,
      `endtime` DATETIME NOT NULL DEFAULT '2099-12-31 23:59:59' ,
      `pin` CHAR(20) NULL DEFAULT NULL ,
      `opts` CHAR(100) NULL DEFAULT NULL ,
      `adminpin` CHAR(20) NULL DEFAULT NULL ,
      `adminopts` CHAR(100) NULL DEFAULT NULL ,
      `members` INT(11) NOT NULL DEFAULT '0' ,
      `maxusers` INT(11) NOT NULL DEFAULT '0' ,
      PRIMARY KEY (`confno`, `starttime`) );
     
     
     
     
    -- -----------------------------------------------------
    -- Table `03talk`.`frontpage_conferences`
    -- -----------------------------------------------------
    DROP TABLE IF EXISTS `frontpage_conferences` ;
     
    CREATE  TABLE IF NOT EXISTS `03talk`.`frontpage_conferences` (
      `id` INT NOT NULL ,
      `meetme_confno` CHAR(80) NOT NULL ,
      `meetme_starttime` DATETIME NOT NULL ,
      `frontpage_user_id` INT NOT NULL ,
      `date` DATETIME NULL ,
      PRIMARY KEY (`id`) ,
      INDEX `fk_frontpage_conferences_meetme1` (`meetme_confno` ASC, `meetme_starttime` ASC) ,
      INDEX `fk_frontpage_conferences_frontpage_users1` (`frontpage_user_id` ASC) ,
      CONSTRAINT `fk_frontpage_conferences_meetme1`
        FOREIGN KEY (`meetme_confno` , `meetme_starttime` )
        REFERENCES `03talk`.`meetme` (`confno` , `starttime` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION,
      CONSTRAINT `fk_frontpage_conferences_frontpage_users1`
        FOREIGN KEY (`frontpage_user_id` )
        REFERENCES `03talk`.`frontpage_users` (`id` )
        ON DELETE NO ACTION
        ON UPDATE NO ACTION)
    ENGINE = InnoDB;
     
     
     
    SET SQL_MODE=@OLD_SQL_MODE;
    SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

    Anybody able to help me with this?

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    You have to insert a row with the key values in 'meetme' first.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    it works just fine when i tested it

    do you already have data in the conferences table? that's probably your problem

    guido, no, you do not have to populate the parent table in order to declare a foreign key
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Sorry I should have explained. I've already done this.

    Example data:

    insert into `03talk`.`meetme` ( `maxusers`, `starttime`, `confno`, `endtime`, `adminpin`, `pin`, `members`) values ( '20', '2010-11-18 14:41:03', '58797451', '2010-11-18 14:41:03', '2323', '2122', '10');

    The first query works.

    I get the error when I then do this:

    insert into `03talk`.`frontpage_conferences` ( `frontpage_user_id`, `meetme_starttime`, `meetme_confno`, `date`) values ( '1', '2010-11-18 14:41:03', '58797451', '2010-11-18 15:14:24');

    (There is already a user with the id of 1 inside the frontpage_users table)

  5. #5
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    I insert into the meetme table first.

    The error occurs when I then try to add into the frontpage_conferences table.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    do us a favour and re-dump your tables, including the test rows they already have
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by r937 View Post
    guido, no, you do not have to populate the parent table in order to declare a foreign key
    The OP said his problem was adding to the child table, not declaring a foreign key. If he can't add to the child table because of the foreign key, doesn't that mean the foreign key value isn't present in the parent table?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    and by the way, why are the columns CHAR and not VARCHAR?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Doh. Worked it out. The meetme table wasn't innodb.

    Working now, cheers for responding so quickly guys

  10. #10
    Non-Member
    Join Date
    Oct 2007
    Posts
    363
    Mentioned
    11 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    and by the way, why are the columns CHAR and not VARCHAR?
    In this instance the meet me table is actually a table that will deal with telephony and not just the web. The table structure is hard coded in c in some of the libraries we are using, so we have to live with what we're given with that one.

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by guido2004 View Post
    If he can't add to the child table because of the foreign key...
    yes, you're right

    i guess i got mixed up about what the problem actually is

    so i tested his two statements --
    Code:
    INSERT 
      INTO `meetme` 
         ( `maxusers`
         , `starttime`
         , `confno`
         , `endtime`
         , `adminpin`
         , `pin`
         , `members` ) 
    VALUES 
         ( '20'
         , '2010-11-18 14:41:03'
         , '58797451'
         , '2010-11-18 14:41:03'
         , '2323'
         , '2122'
         , '10' );
    
    INSERT 
      INTO `frontpage_conferences` 
         ( `frontpage_user_id`
         , `meetme_starttime`
         , `meetme_confno`
         , `date` ) 
    VALUES 
         ( '1'
         , '2010-11-18 14:41:03'
         , '58797451'
         , '2010-11-18 15:14:24' );
    and it worked just fine (yes, the FK was defined)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •