davewood

This entry demonstrates how to implement a relationship with optional foreign key values using DBIx::Class.

Imagine two related tables 'human' and 'cat': A cat belongs to a human but when the human dies the cat can continue to roam freely.

package MyApp::Schema::Result::Human;
use strict;
use warnings;
use base 'DBIx::Class';
__PACKAGE__->load_components(qw/ Core /);
__PACKAGE__->table('human');
__PACKAGE__->add_columns(
    id => {
        data_type         => 'integer',
        is_auto_increment => 1,
        is_numeric        => 1,
    },
);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->has_many(
    'cats',
    'MyApp::Schema::Result::Cat',
    'human_id',
    { cascade_delete => 0 },
);
1;

package MyApp::Schema::Result::Cat;
use strict;
use warnings;
use base 'DBIx::Class';
__PACKAGE__->load_components(qw/ Core /);
__PACKAGE__->table('cat');
__PACKAGE__->add_columns(
    id => {
        data_type         => 'integer',
        is_auto_increment => 1,
        is_numeric        => 1,
    },
    human_id => {
        data_type      => 'int',
        is_numeric     => 1,
        is_foreign_key => 1,
        is_nullable    => 1,
    },
);
__PACKAGE__->set_primary_key('id');
__PACKAGE__->belongs_to(
    'human',
    'MyApp::Schema::Result::Human',
    'human_id',
    { join_type => 'left', on_delete => 'SET NULL' },
);
1;

Notes

• the 'has_many' relationship in table 'human' has the attribute "cascade_delete => 0", this means that if $human has a $cat and $human is deleted, $cat will not be deleted.


• the 'human_id' column in table 'cat' is nullable so a cat can exist without having a owner/human.


• the 'human_id' column in table 'cat' is a foreign key, a foreign key constraint will be created.


• the belongs_to' relationship in table 'cat' has the attribute "join_type => 'left'", this makes sure that relatonship traversal for a relationship with optional foreign key value works consistently in all situations.


• the 'belongs_to' relationship in table 'cat' has the attribute "on_delete => 'SET NULL'", this causes the column 'human_id' in table 'cat' to be set to NULL if the related human row is deleted.

I was asked why the 'on_delete' attribute is on the Cat class and not on the Human.

Answer:
'SET NULL' causes the referencing column in the referencing row to be set to null when the referenced row is deleted.
(paraphrased from: http://www.postgresql.org/docs/9.3/static/ddl-constraints.html)

ALTER TABLE "cat" ADD CONSTRAINT "cat_fk_human_id" FOREIGN KEY ("human_id") REFERENCES "human" ("id") ON DELETE SET NULL DEFERRABLE:

Testing

use Test::More;
use Test::Fatal qw/ exception /;
my $human = $schema->resultset('Human')->create({});
my $cat   = $schema->resultset('Cat')->create({ human_id => $human->id });
is(
    exception { $human->delete; },
    undef,
    'human can be deleted even though used in cat.'
);
ok(!$human->in_storage, "human not in storage");
$cat->discard_changes(); # refresh from database
is($cat->human_id, undef, 'human_id has been set to NULL');