CODEWITHSUNDEEP
phpforeign-keysconstraintspropelcascade
LeonardChallis
LeonardChallis

Reputation: 7783

Why is my propel cascade blocked by foreign key constraints?

I have the following structure in the databse:

enter image description here

In my code I first create a Company, then a logbook, then a number of schedules, then a user, then a logbook responsibility. Sort of like this (not exact code for conciseness, but I don't think that matters:

<?php

$company = new Company();
$company->setSomething(123);

$logbook = new Logbook();
$logbook->setSomething('abc');
$logbook->setCompany($company);

$schedules = array();
for ($x=0; $x<$something; $x++) {
  $schedule = new Schedule();
  $schedule->setSomething(doSomethingWithSomething($something[$x]);
  $schedule->setLogbook($logbook);
  $schedules[] = $schedule;
}

$user = new User();
$user->setSomething('something');
$user->setCompany($company);
$user->setCurrentLogbook($logbook);

$logbookResponsibility = new LogbookResponsibility();
$logbookResponsibility->setLogbook($logbook);
$logbookResponsibility->setResponsibilityId(1);
$logbookResponsibility->setUser($user);

$errors = someFormOfCheck();

if (!$errors) {
  $user->save();
  $logbookResponsibility->save();
  foreach ($schedules as $schedule) {
    $schedule->save();
  }
}

I get the following error:

<b>Fatal error</b>:  Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`logbook`.`logbook_responsibility`, CONSTRAINT `fk_logbook_responsbility_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE)' in C:\wamp\bin\php\php5.3.8\pear\propel\connection\DebugPDOStatement.php:90

The logbook_responsibility table is made up of three foreign keys, each which have a RESTRICT on the updates, because I don't want it to be possible to delete a user, logbook or type of responsibility if a logbook responsibility exists. I believe the problem, however, is determined by the order of the cascade.

If I take out the $logbookResponsibility parts altogether, it works perfectly. If I put the $logbookResponsibility parts after the $user->save(); and then issue a $logbookResponsibility->save(); as well, that also works perfectly.

My question, then, is where abouts am I going wrong? What am I presuming incorreclty or doing wrong, to make the cascade work? Or is there something else that might be stopping it?

Update: If I do the following, it works just fine:

$company->save();
$logbook->save();
$user->save();
$logbookResponsibility->save();
foreach ($schedules as $scheudle) {
  $schedule->save();
}

i.e. if I save them manually first instead of relying on the cascade.

Upvotes: 2

Views: 1113

Answers (1)

Emmeram Morning
Emmeram Morning

Reputation: 101

I can't give you a good, direct answer to the exact problem you're seeing, but I've encountered similarly confusing issues with foreign keys and relationships in Propel. My best practice has now become setting IDs manually rather than allowing Propel to attempt to manage setting them through defined relationships. In this case, that would mean not calling $logbookResponsibility->setLogbook() or $logbookResponsibility->setUser() at all. Instead, call $logbookResponsibility->setLogbookId() and $logbookResponsibility->setUserId() after you have called save() on $user and $logbook.

Propel does a lot of things by reference, which can have unintended consequences when you're dealing with foreign keys and are setting relationships by calling methods expecting objects (e.g. setLogbook()) rather than methods expecting the relationship's ID (e.g. setLogbookId()).

Upvotes: 2

Related Questions