Well, I found a solution to my problem. I'm not sure it is the most
elegant, but here goes:
I figured that just putting the proper cascade logic into my actual
DB schema would fix things, and it does. I changed my table
definition to:
84 CREATE TABLE `campaign_clients` (
85 `campaign_id` int(32) unsigned NOT NULL COMMENT '',
86 `client_id` int(32) unsigned NOT NULL COMMENT '',
87 FOREIGN KEY (`campaign_id`) REFERENCES campaigns(id) ON DELETE
CASCADE ON UPDATE CASCADE,
88 FOREIGN KEY (`client_id`) REFERENCES clients(id) ON DELETE
CASCADE ON UPDATE CASCADE
89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
COMMENT='' AUTO_INCREMENT=0;
Obviously I could also have just ALTERed the tables (in case you
don't want to trash all your data just to fix this silly little
problem):
ALTER TABLE `campaign_clients` ADD CONSTRAINT `campaign_clients`
FOREIGN KEY (`campaign_id`) REFERENCES `campaigns` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `campaign_clients` FOREIGN
KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE ON
UPDATE CASCADE;
I think a better solution (TIMTOWTDI) is to for DBIx to somehow
automatically detect the constraint and transact a delete that
removes the child rows first and then the parent rows, in order to
make sure everything gets deleted. I think that one is a pipe dream
so I'll happily use my solution above for now.
-d
Dustin Suchter wrote:
> This is only slightly modified code from the tutorial on CPAN. I
> probably mis-copied something and hence my problem, but I can't seem
> to figure out what.
>
> I have a a controller called Campaigns.pm with the delete method
> below (comments and empty lines removed):
>
> 124 sub delete : Local {
> 126 my ($self, $c, $id) = @_;
> 129 $c->model('AdBlueDB::Campaign')->search({id =>
> $id})->delete_all;
> 132 $c->flash->{status_msg} = 'Campaign deleted';
> 135 $c->response->redirect($c->uri_for('/campaigns/list'));
> 136 }
>
> My DB model Campaign.pm has the necessary has_many relationship to
> populate a relationship table in my db:
>
> 23 __PACKAGE__->has_many(campaign_client =>
> 'AdBlueDB::CampaignClient', 'campaign_id');
>
> My SQL is as follows:
>
> 84 CREATE TABLE `campaign_clients` (
> 85 `campaign_id` int(32) unsigned NOT NULL
> COMMENT '',
> 86 `client_id` int(32) unsigned NOT NULL
> COMMENT '',
> 87 FOREIGN KEY (`campaign_id`) REFERENCES campaigns(id),
> 88 FOREIGN KEY (`client_id`) REFERENCES clients(id)
> 89 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
> COMMENT='' AUTO_INCREMENT=0;
>
> I can create new campaigns and the primary "campaign" table gets all
> the right stuff along with the "campaign_client" table (it also gets
> the right relationship data and stores it properly).
>
> I'm wondering why I get the error:
>
> DBIx::Class::ResultSet::delete_all(): DBI Exception: DBD::mysql::st
> execute failed: Cannot delete or update a parent row: a foreign key
> constraint fails [for Statement "DELETE FROM campaigns WHERE ( id =
> ? )" with ParamValues: 0='47'] at... AdBlue/Controller/Campaigns.pm
> line 129
>
> when I use my delete method?
>
> From what I can tell the DBIx::Class::Relationship documentation
> indicates that the dependent child tables will be deleted
> automatically unless I specifically turn off cascade_delete, so why
> am I getting an error that I essentially translate as "I can't
> delete this row because it has child dependencies"?
>
> http://cpan.uwinnipeg.ca/htdocs/DBIx-Class/DBIx/Class/Relationship.html#has_many
>
> "If you delete an object in a class with a has_many relationship,
> all the related objects will be deleted as well. To turn this
> behaviour off, pass cascade_delete => 0 in the $attr hashref.
> However, any database-level cascade or restrict will take precedence
> over a DBIx-Class-based cascading delete."
>
>
> I know this has got to be something simple, so I guess I haven't
> wrapped my head around the DB relationships yet. The only somewhat
> significant difference I can find between my SQL and the CPAN
> example is that I use Foreign Keys and I don't see that in the
> example. Dunno if that matters though...
>
> thanks!
> -d
>
_______________________________________________
List: Catalyst@lists.rawmode.org
Listinfo: http://lists.rawmode.org/mailman/listinfo/catalyst
Searchable archive: http://www.mail-archive.com/catalyst@lists.rawmode.org/
Dev site: http://dev.catalyst.perl.org/
