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.

Subscribe2 Comments on Learning CakePHP: Foreign Key...

  1. Dieter_be said...

    Why do you use the varchar datatype?
    I don’t allow users to enter “no relation” so I’m not sure, but if you use a proper datatype for id’s and foreign keys (like unsigned int), Cake might be smart and use null instead of an empty string for unset FK’s.
    If it doesn’t do that, I suggest to file a bug report.

  2. Rob Wilkerson said...

    Readability. locations is really just a lookup table. Whenever possible, I like to be able to open a table that uses lookup data and have it be meaningful without requiring the extra step of writing SQL to join the lookup. In this case, opening the events table would show a location like MAINTENT. Just by looking at the event record, I can tell where it’s happening.

    You’re right that Cake may be smart enough if it was an int, but I use UUIDs. That’s just a char datatype so it may not behave any differently. It’s a good point to test, though. I may try it.