Learning CakePHP: Foreign Key Constraint Violation
In the course of building forms with CakePHP’s form helper, I kept running into problems with the foreign key constraints in my database. I’d get an error if I didn’t enter a value in the form even though a NULL value was allowed at the database level. A little debugging indicated that the cause was CakePHP trying to insert an empty string into the foreign key field. Since there was no record in the parent table with an empty string id value, the database had no choice but to reject the insert request:
SQL Error: 1452: Cannot add or update a child row: a foreign key constraint fails (`mydatabase/events`, CONSTRAINT `events_ibfk_1` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE)
I will confess right now that I didn’t research this extensively and I don’t know whether this is something that’s easily avoidable using existing techniques or resources offered by CakePHP. It could also be that I’ve coded something incorrectly. What I saw, though, was an opportunity to learn. I saw an opportunity to create a behavior – something I hadn’t done yet – that would detect fields where a NULL value was acceptable and replace empty string values accordingly.
My project has events and events have locations. The relevant components of my database schema look like this:
CREATE TABLE locations (
id VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
description VARCHAR(255) NULL,
PRIMARY KEY ( id )
);
CREATE TABLE events (
id CHAR(36) NOT NULL,
location_id VARCHAR(255) NULL,
name VARCHAR(255) NOT NULL,
start_time DATETIME NOT NULL,
end_time DATETIME NULL,
PRIMARY KEY ( id ),
FOREIGN KEY ( location_id )
REFERENCES locations ( id )
ON UPDATE CASCADE
ON DELETE SET NULL
);Using those tables are my Event and Location models:
class Event extends AppModel {
public $name = ‘Event’;
public $belongsTo = array ( ‘Location’ );
/** content */
}
class Location extends AppModel {
public $name = ‘Location’;
public $hasMany = ‘Event’;
/** content */
}In my event form, I have a dropdown list by which a user can select a location where the event will occur. Since not all events have a specific location, I’ve set the empty option to true so that the user can choose no association:
echo $form->input (
'location_id',
array (
'div' => 'input select',
'selected' => $this->data['Location']['id'],
'empty' => true
)
);Unfortunately, my foreign key constraint didn’t like that empty option. My solution was to create a new Nullable behavior. All the behavior does is inspect a model’s schema and the data being saved. If a field is nullable and the data for that field is empty, the data values empty string is replaced with a null value. It does this in the beforeSave() callback function.
class NullableBehavior extends ModelBehavior {
/**
* function beforeSave
* Looks for nullable fields in the schema and replaces empty string values for those fields
* with NULL values.
*/
function beforeSave ( $model ) {
$schema = $model->schema();
foreach ( $schema as $field => $metadata ) {
if ( $metadata[‘null’] ) {
if ( isset ( $model->data[$model->name][$field] ) && $model->data[$model->name][$field] === ‘’ ) {
$model->data[$model->name][$field] = null;
}
}
}
}
}Because I really like the technical accuracy that results from this solution even when constraint errors aren’t being thrown, I wanted to apply it to all of my models. Because I’m lazy, I didn’t want to write the code to add the behavior to all of those models. What I did instead was add it to my applications AppModel in the app/ directory.
class AppModel extends Model{
public $actsAs = array ( 'Nullable' );
}It solved my problem, but it may not be perfect or even necessary. Any feedback on my approach or the behavior itself would be appreciated.