"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:


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? :slight_smile:

You have to insert a row with the key values in ‘meetme’ first.

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

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)

I insert into the meetme table first.

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

do us a favour and re-dump your tables, including the test rows they already have

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?

and by the way, why are the columns CHAR and not VARCHAR?

Doh. Worked it out. The meetme table wasn’t innodb.

Working now, cheers for responding so quickly guys :slight_smile:

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.

yes, you’re right

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

so i tested his two statements –

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)