romain.dorgueil.net
10 Jul 2008

database Foreign key's onDelete for dummies

by Romain Dorgueil

Any modern relational database engine supports the onDelete and onUpdate attributes on foreign keys. Propel and Doctrine of course allows to define it in the schema file (yaml or xml) and I believe it is important to explicitly set it.

Restrict mode

«Better do nothing than something stupid.»

The default behaviour is ON DELETE RESTRICT. This is the most secure setting, so it is normal to have it as default.

If we try to DELETE a record referenced by the current object, the database engine just raise an SQL error. If it was not an error to DELETE this, you will have to start by removing the linked object, or setting the foreign key value to something different.

Cascade mode

«No need to keep obsolete things.»

But our database engines are able to do better. For example, if you have an user's settings table and you delete an user, you may not wan't to keep his settings. You just set ON DELETE CASCADE on user's setting foreign key column referencing the user table, and if a user is ever deleted, all his associated properties will be destroyed.

Along with ON DELETE CASCADE, we usually add a NOT NULL constraint (or required: true), as the existence of current object is directly related to the existence of linked object. This may be wrong from time to time, but it is usually the case.

Set null mode

«We don't rely on this. Just forget it.»

The last behavior is the ON DELETE SET NULL option. It does exactly what it says. If linked object is removed, we set our foreign key value to null.

This the behavior you may want to use for articles having a link to their author for example. If the author is removed from database, we don't want to delete his articles, so we just loose the author information (well ok, this is a simplification, but it basically shows the idea).

Why I think we should explicitly use it

The default database engine behavior is «I don't know how to handle it». Setting it will add power to your database model, while taking profit of your RDBMS features. Not setting it is like saying «I don't know which way I want my database to behave in case we delete a linked object», and that's like saying «I don't know how I want my model to react». Thinking a little to understand which way you want it to work will make you certain you're understanding the behavior you need in your project.

How to set it in your favorite ORM

schema.yml with Doctrine

Article:
  columns:
    title:   string(255)
    body:    string(4096)
    user_id: integer(4)
  relations:
    User:
      onDelete: CASCADE

PHP model with Doctrine

<?php
public function setUp()
{
  $this->hasOne('User', array('local'    => 'user_id',
                              'foreign'  => 'id',
                              'onDelete' => 'CASCADE'));
}

schema.yml with Propel

    data_id:  { type: integer, primaryKey: true, foreignTable: data, foreignReference: id, onDelete: cascade, onUpdate: cascade }

schema.xml with Propel

<foreign-key foreignTable="data" onDelete = "setnull">
  <reference local="data_id" foreign="id" />
</foreign-key>

Join the discussion