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

Yii 1.1 General Discussion
1

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;

2

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

3

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.

4

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

5

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.

6

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.

7

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.