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)

---

In reply to Using DBIx::Class To Delete Row With Foreign Keys by varanasi

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.