Set foreign column to NULL when related row is deleted

I have users with two optional pictures (don't worry about the logic, the names have been changed to protect the innocent):

CREATE TABLE users (
    id INT(10) NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    first_picture_id INT(10) NULL DEFAULT NULL,
    second_picture_id INT(10) NULL DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT FK_users_second_picture_id FOREIGN KEY (second_picture_id) REFERENCES pictures (id),
    CONSTRAINT FK_users_first_picture_id FOREIGN KEY (first_picture_id) REFERENCES pictures (id)
);

CREATE TABLE pictures (
    id INT(10) NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    path VARCHAR(255) NOT NULL,
    type VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

I've linked my models this way (I hope that part's correct):

class User extends AppModel{
    public $belongsTo = array(
        'FirstPicture' => array(
            'className' => 'Picture',
            'foreignKey' => 'first_picture_id',
        ),
        'SecondPicture' => array(
            'className' => 'Picture',
            'foreignKey' => 'second_picture_id',
        ),
    );
}

class Picture extends AppModel{
    public $hasOne = array(
        'User',
    );
}

Now, when I remove either picture:

$this->FirstPicture->delete($this->request->data('FirstPicture.id'));

... I want to set the corresponding column in user set to NULL.

What'd be the CakePHP idiom (if any) to replicate an ON DELETE SET NULL foreign key deletion?

There is no functionality built into CakePHP 2.x that would automatically do that, you'd have to implement that on your own. Whenever possible it would be advised to use actual foreign key constraints in your database, but if that's not possible, then you don't really have much options here.

It should be easy enough doing that in the beforeDelete or afterDelete events/callbacks. If your database supports transactions (and your app uses them - they don't happen automatically for delete operations), then beforeDelete is advised as it can easily stop the deletion process. Without transactions, it would depend on what you like better in an error scenario, nulled foreign keys without the associated records being deleted (beforeDelete), or deleted associated records with non-nulled foreign keys (afterDelete).

Here's a simple example, you could do it from either side of the association, Picture:

public function beforeDelete($cascade = true) {
    if (!parent::beforeDelete($cascade)) {
        return false;
    }

    $result = $this->User->updateAll(
        array('first_picture_id' => null),
        array('first_picture_id' => $this->getID())
    );
    if (!$result) {
        return false;
    }

    $result = $this->User->updateAll(
        array('second_picture_id' => null),
        array('second_picture_id' => $this->getID())
    );
    if (!$result) {
        return false;
    }

    return true;
}

or User:

public function __construct($id = false, $table = null, $ds = null)
{
    parent::__construct($id, $table, $ds);

    $this->FirstPicture->getEventManager()->attach(
        function () {
            return $this->updateAll(
                array('first_picture_id' => null),
                array('first_picture_id' => $this->FirstPicture->getID())
            );
        },
        'Model.beforeDelete'
    );

    $this->SecondPicture->getEventManager()->attach(
        function () {
            return $this->updateAll(
                array('second_picture_id' => null),
                array('second_picture_id' => $this->SecondPicture->getID())
            );
        },
        'Model.beforeDelete'
    );
}