Yii Framework Forum: 1452 Cannot add or update a child row: a foreign key constraint fails - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1452 Cannot add or update a child row: a foreign key constraint fails Rate Topic: -----

#1 User is offline   haymps 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 11-June 12
  • Location:Wake Forest

Posted 19 August 2012 - 08:54 PM

1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`user_has_project`, CONSTRAINT `fk_user_has_project_project1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE). The SQL statement executed was: INSERT INTO `user_has_project` (`project_id`, `user_id`) VALUES (:yp0, :yp1)

$userhasProject = new UserHasProject;
$userhasProject->project_id = $id;
$userhasProject->user_id = $user_id;
if (!$userhasProject->save()) print_r ($project->getErrors());


I have both IDs saved in the DB. That is $id (project id) and $user_id. All IDs are int(11).


I'm generating the table from MySQL workbench:

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 SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`user_has_project`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`user_has_project` (
`user_id` INT NOT NULL ,
`project_id` INT NOT NULL ,
`newtitle_id` INT NULL ,
`note_id` INT NULL ,
INDEX `fk_user_has_project_project1` (`project_id` ASC) ,
INDEX `fk_user_has_project_user1` (`user_id` ASC) ,
INDEX `fk_user_has_project_newtitle1` (`newtitle_id` ASC) ,
INDEX `fk_user_has_project_note1` (`note_id` ASC) ,
CONSTRAINT `fk_user_has_project_user1`
FOREIGN KEY (`user_id` )
REFERENCES `mydb`.`user` (`id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_user_has_project_project1`
FOREIGN KEY (`project_id` )
REFERENCES `mydb`.`project` (`id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_user_has_project_newtitle1`
FOREIGN KEY (`newtitle_id` )
REFERENCES `mydb`.`newtitle` (`id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT,
CONSTRAINT `fk_user_has_project_note1`
FOREIGN KEY (`note_id` )
REFERENCES `mydb`.`note` (`id` )
ON DELETE RESTRICT
ON UPDATE RESTRICT)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
0

#2 User is offline   PatrickM 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 14-July 12

Posted 20 August 2012 - 02:30 AM

Can you enable logging and have a look at what values are used for the parameters (:yp0, :yp1)?
0

#3 User is offline   Da:Sourcerer 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 1,193
  • Joined: 30-March 11
  • Location:Berlin, Germany

Posted 20 August 2012 - 02:55 AM

The assigned project_id must be wrong (cf the documentation on error 1452). In general, tables like x_has_y are best managed by extensions such as the WithRelatedBehavior.
programmer /ˈprəʊgramə/, noun: a device that converts ►coffee into ►code
0

#4 User is offline   haymps 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 11-June 12
  • Location:Wake Forest

Posted 20 August 2012 - 08:04 AM

View PostPatrickM, on 20 August 2012 - 02:30 AM, said:

Can you enable logging and have a look at what values are used for the parameters (:yp0, :yp1)?


Here's what's in application.log:

2012/08/20 08:59:07 [error] [system.db.CDbCommand] CDbCommand::execute() failed: SQLSTATE[HY000]: General error: 1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`user_has_project`, CONSTRAINT `fk_user_has_project_project1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE). The SQL statement executed was: INSERT INTO `user_has_project` (`project_id`, `user_id`) VALUES (:yp0, :yp1).
in C:\xampp\htdocs\yii\cml\protected\controllers\ProjectController.php (153)
in C:\xampp\htdocs\yii\cml\index.php (13)
2012/08/20 08:59:07 [error] [exception.CDbException] exception 'CDbException' with message 'CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1452 Cannot add or update a child row: a foreign key constraint fails (`mydb`.`user_has_project`, CONSTRAINT `fk_user_has_project_project1` FOREIGN KEY (`project_id`) REFERENCES `project` (`id`) ON DELETE CASCADE ON UPDATE CASCADE). The SQL statement executed was: INSERT INTO `user_has_project` (`project_id`, `user_id`) VALUES (:yp0, :yp1)' in C:\xampp\htdocs\yii\cml\framework\db\CDbCommand.php:354
Stack trace:
#0 C:\xampp\htdocs\yii\cml\framework\db\ar\CActiveRecord.php(1014): CDbCommand->execute()
#1 C:\xampp\htdocs\yii\cml\framework\db\ar\CActiveRecord.php(787): CActiveRecord->insert(NULL)
#2 C:\xampp\htdocs\yii\cml\protected\controllers\ProjectController.php(153): CActiveRecord->save()
#3 [internal function]: ProjectController->actionFollow('4588')
#4 C:\xampp\htdocs\yii\cml\framework\web\actions\CAction.php(107): ReflectionMethod->invokeArgs(Object(ProjectController), Array)
#5 C:\xampp\htdocs\yii\cml\framework\web\actions\CInlineAction.php(48): CAction->runWithParamsInternal(Object(ProjectController), Object(ReflectionMethod), Array)
#6 C:\xampp\htdocs\yii\cml\framework\web\CController.php(309): CInlineAction->runWithParams(Array)
#7 C:\xampp\htdocs\yii\cml\framework\web\filters\CFilterChain.php(134): CController->runAction(Object(CInlineAction))
#8 C:\xampp\htdocs\yii\cml\framework\web\filters\CFilter.php(41): CFilterChain->run()
#9 C:\xampp\htdocs\yii\cml\framework\web\CController.php(1146): CFilter->filter(Object(CFilterChain))
#10 C:\xampp\htdocs\yii\cml\framework\web\filters\CInlineFilter.php(59): CController->filterAccessControl(Object(CFilterChain))
#11 C:\xampp\htdocs\yii\cml\framework\web\filters\CFilterChain.php(131): CInlineFilter->filter(Object(CFilterChain))
#12 C:\xampp\htdocs\yii\cml\framework\web\CController.php(292): CFilterChain->run()
#13 C:\xampp\htdocs\yii\cml\framework\web\CController.php(266): CController->runActionWithFilters(Object(CInlineAction), Array)
#14 C:\xampp\htdocs\yii\cml\framework\web\CWebApplication.php(276): CController->run('follow')
#15 C:\xampp\htdocs\yii\cml\framework\web\CWebApplication.php(135): CWebApplication->runController('project/follow')
#16 C:\xampp\htdocs\yii\cml\framework\base\CApplication.php(162): CWebApplication->processRequest()
#17 C:\xampp\htdocs\yii\cml\index.php(13): CApplication->run()
#18 {main}
REQUEST_URI=/yii/cml/index.php/project/follow/4588

I know this is a MySQL constraint, as I cannot add the entry even manually via phpMyAdmin
0

#5 User is offline   haymps 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 11-June 12
  • Location:Wake Forest

Posted 20 August 2012 - 08:06 AM

View PostDa:Sourcerer, on 20 August 2012 - 02:55 AM, said:

The assigned project_id must be wrong (cf the documentation on error 1452). In general, tables like x_has_y are best managed by extensions such as the WithRelatedBehavior.


Thanks. I was using:
// return array('CSaveRelationsBehavior' =>
// array('class' => 'ext.yiiext.behaviors.activerecord-relation.EActiveRecordRelationBehavior'));

But had the same error. So I went with brute force. Now I have tried in manually with phpMyAdmin and I get the same error. Time to learn more about Foreign Keys and how they work.
0

#6 User is offline   PatrickM 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 23
  • Joined: 14-July 12

Posted 21 August 2012 - 03:10 AM

Are you still working on this one? I am new to Yii myself so can only advise on the SQL side of things.

Try enabling "trace" mode in your logging:
'class'=>'CLogRouter',
  'routes'=>array(
  array(
    'class'=>'CFileLogRoute',
    'levels'=>'error, warning, trace',
  ),


That should give you something like this in your log file:

2012/08/21 09:26:00 [trace] [system.db.CDbCommand] Executing SQL: INSERT INTO `touritems` (`tour_id`, `name`) VALUES (:yp0, :yp1). Bound with :yp0=1068, :yp1='test'

Observe the "bound with" part, that's probably where your FK is failing.
0

#7 User is offline   haymps 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 11-June 12
  • Location:Wake Forest

Posted 21 August 2012 - 09:13 AM

View PostPatrickM, on 21 August 2012 - 03:10 AM, said:

Are you still working on this one? I am new to Yii myself so can only advise on the SQL side of things.

Try enabling "trace" mode in your logging:
'class'=>'CLogRouter',
  'routes'=>array(
  array(
    'class'=>'CFileLogRoute',
    'levels'=>'error, warning, trace',
  ),


That should give you something like this in your log file:

2012/08/21 09:26:00 [trace] [system.db.CDbCommand] Executing SQL: INSERT INTO `touritems` (`tour_id`, `name`) VALUES (:yp0, :yp1). Bound with :yp0=1068, :yp1='test'

Observe the "bound with" part, that's probably where your FK is failing.


Patrick, thank you for your help. I am new to both Yii and MySQL. So all info is greatly appreciated.

I think this fixed it. But I don't know what it is doing differently in the SQL statements:

I added this to the model class for the table I was having the issue with:

public function primaryKey() {
return array('user_id','project_id');

I will add CLogRouter. I'm assuming it is added to the main.php config file.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users