Using multiple column primary key in CakePHP (cascade delete problem)

ReviewI guess you stumbled here because you want to know can you use a primary key in your CakePHP model made of two or more columns.
Well… You can’t, at least in the the CakePHP 1.3, at least I couldn’t, and if you can, let me know, because that would be awesome…

Problem

Anyway, I know I tried to have a table like this:

CREATE TABLE `projects_users`
(
    `project_id` int(10) unsigned NOT NULL,
    `user_id`    int(10) unsigned NOT NULL,
    `attr`     int(10) unsigned NOT NULL,
    PRIMARY KEY (`project_id`,`user_id`)
);

Unlike in all CakePHP projects, I wanted to have a regular HABTM mid-table like I would have had in SQL Server for example. I even saw a web-page (instead of reading this) that described how to use multi-column primary key in CakePHP by adding the $primaryKey value (like below) to your model (in my case “ProjectsUser”):

<?php
class ProjectsUser extends AppModel
{
    var $name = 'ProjectsUser';
    var $primaryKey = array('project_id','user_id');
    var $belongsTo = array
    (
        'Project' => array
        (
            'className' => 'Project',
            'foreignKey' => 'project_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'User' => array
        (
            'className' => 'User',
            'foreignKey' => 'user_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
    );
}
?>

Great success… NOT.

So I’ve been working and working on the MVC’s related to this this table, with no problems, until I saw that the cascade delete didn’t work on this table. I didn’t even saw any errors displayed, but it just didn’t work.

A heap of smeggin hours (actually it took 15 minutes) of debugging cake modules, until I discovered that when deleting HABTM items, CakePHP AppModel executes this query:

select `ProjectsUser`.`id` from `projects_users` as `ProjectsUser` where `ProjectsUser`.`project_id` = 17

and fails afterwards, thus not deleting the records from the HABTM table.

Solution

I modified the table script:

CREATE TABLE `projects_users`
(
    `id`         int(10) unsigned NOT NULL auto_increment,
    `project_id` int(10) unsigned NOT NULL,
    `user_id`    int(10) unsigned NOT NULL,
    `attr`     int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`project_id`,`user_id`)
);

I removed the $primaryKey from my model:

<?php
class ProjectsUser extends AppModel
{
    var $name = 'ProjectsUser';
    var $belongsTo = array
    (
        'Project' => array
        (
            'className' => 'Project',
            'foreignKey' => 'project_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        ),
        'User' => array
        (
            'className' => 'User',
            'foreignKey' => 'user_id',
            'conditions' => '',
            'fields' => '',
            'order' => ''
        )
    );
}
?>

And the cascade delete stared to work…

Bonus

What’s puzzling the first table/model version cascade delete worked while I didn’t have the the third column (`attr`) in the database. If that works for you, try it, but I wouldn’t suggest it.

Comments are closed.