Using DBIx::Class To Delete Row With Foreign Keys

varanasi has asked for the wisdom of the Perl Monks concerning the following question:

Using DBIx::Class, I would like to delete a row from a table that contains a field referencing a foreign key. When I try, get this error message (via Catalyst):

DBIx::Class::Storage::DBI::_dbh_execute(): DBI Exception: DBD::SQLite:
+:st execute failed: FOREIGN KEY constraint failed [for Statement "DEL
+ETE FROM member WHERE ( id = ? )" with ParamValues: 1=6] at /home/mar
+k/80-004 Test/script/../lib/Test/Controller/Member/Manage.pm line 91
[download]

Would you be kind enough to help me? This problem has harassed me before. Normally, I simply delete away, and life is fine. I don't really know what I'm doing and just trust DBIx::Class to take care of me. Most of the time, it does. Then, the problem pops up, and I mangle things until I get something to work. I've looked at code that works and compared it to code that doesn't, and I'm too inexperienced to see a difference. The time has come for me to finally figure out what is going on.

Specifically, I would like to delete a row from table Member that references a row in table User. I don't wish to touch the row in User. Here's the line I'm using to do the delete:

my $member_obj = $c->model('DB::Member')->find({ id => $member_id });
$member_obj->delete;
[download]

Here are the relevant part of my Schema and database sql files (slimmed down):

package Test::Schema::Result::Member;
. . .
__PACKAGE__->add_columns(
  "id",
  { data_type => "integer", is_auto_increment => 1, is_nullable => 0},
  "user_id",
  { data_type => "integer", is_foreign_key => 1, is_nullable => 1 },


__PACKAGE__->belongs_to(
  "user",
  "Test::Schema::Result::User",
  { id => "user_id" },
  {
    is_deferrable => 0,
    join_type     => "LEFT",
    on_delete     => "NO ACTION",
    on_update     => "NO ACTION",
  },
);
[download]

package Test::Schema::Result::User;

. . . 

__PACKAGE__->has_many(
  "members",
  "Test::Schema::Result::Member",
  { "foreign.user_id" => "self.id" },
  { cascade_copy => 0, cascade_delete => 0 },
);
[download]

CREATE TABLE member (
        id          INTEGER PRIMARY KEY,
        user_id     INTEGER REFERENCES user(id)
);

CREATE TABLE user (
    id INTEGER PRIMARY KEY,
    username TEXT
);
[download]

2019-01-25 Athanasius fixed first code block (line was too wide)

Comment on Using DBIx::Class To Delete Row With Foreign Keys
Select or Download Code

Replies are listed 'Best First'.
Re: Using DBIx::Class To Delete Row With Foreign Keys by 1nickt (Canon) on Jan 24, 2019 at 16:35 UTC
Hi, I would allow DBIx::Class to manage the relationships, i.e. try removing '`REFERENCES user(id)`' from your table schema. It's creating a database foreign key which is preventing the deletion, and as the doc says, By default, DBIx::Class cascades deletes across has_many, has_one and might_have relationships. You can disable this behaviour on a per-relationship basis by supplying cascade_delete => 0 in the relationship attributes. The cascaded operations are performed after the requested delete, so if your database has a constraint on the relationship, it will have deleted/updated the related records or raised an exception before DBIx::Class gets to perform the cascaded operation. Hope this helps! The way forward always starts with a minimal test.	[reply] [d/l]
Re^2: Using DBIx::Class To Delete Row With Foreign Keys by varanasi (Scribe) on Jan 24, 2019 at 17:23 UTC
Thanks. Likely I don't understand how cascades work. I thought using a cascade would delete my row in User if I delete the Member row that uses that key.	[reply]
Re: Using DBIx::Class To Delete Row With Foreign Keys by poj (Abbot) on Jan 24, 2019 at 16:26 UTC
Are there any other tables which use `member.user_id` and have constraints ? poj	[reply] [d/l]
Re^2: Using DBIx::Class To Delete Row With Foreign Keys by varanasi (Scribe) on Jan 24, 2019 at 17:20 UTC
Yes! There is one table, Roster, that does: package Test::Schema::Result::Roster; . . . __PACKAGE__->add_columns( "id", { data_type => "integer", is_auto_increment => 1, is_nullable => 0}, "member_id", { data_type => "integer", is_foreign_key => 1, is_nullable => 1 }, ); . . . __PACKAGE__->belongs_to( "member", "Test::Schema::Result::Member", { id => "member_id" }, { is_deferrable => 0, join_type => "LEFT", on_delete => "NO ACTION", on_update => "NO ACTION", }, ); ------------sql------ CREATE TABLE roster ( id INTEGER PRIMARY KEY, member_id INTEGER REFERENCES member(id) ); [download]	[reply] [d/l]
Re^3: Using DBIx::Class To Delete Row With Foreign Keys by poj (Abbot) on Jan 24, 2019 at 17:31 UTC
In that case, delete the roster records before deleting the member. poj	[reply]
Re^4: Using DBIx::Class To Delete Row With Foreign Keys by varanasi (Scribe) on Jan 24, 2019 at 17:48 UTC


Welcome to the Monastery
	PerlMonks