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

[aaarrrggh]

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?

[guido2004]

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

[r937]

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

[aaarrrggh]

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)

[aaarrrggh]

I insert into the meetme table first.

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

[r937]

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

[guido2004]

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?

[r937]

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

[aaarrrggh]

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

Working now, cheers for responding so quickly guys

[aaarrrggh]

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.

[r937]

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

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)