While deploying a schema to a SQL Server 2005 database today I ran
into this database error:
Msg 1785, Level 16, State 0, Line 1236
Introducing FOREIGN KEY constraint 'tblItemCategory_fk_item_id' on
table 'tblItemCategory' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other
FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1236
Could not create constraint. See previous errors.
OK, so the workaround is to switch off cascading for the constraints
in question. I had a look at the docs for DBIx::Class::Relationship,
which state:
"Cascading deletes are off by default on a belongs_to relationship. To
turn them on, pass cascade_delete => 1 in the $attr hashref."
The default behaviour is not as documented - I had not specified any
cascade behaviour for the constraint. Setting cascade_delete => 0 or
cascade_delete => undef had no effect on the generated SQL, which
still contained "ON DELETE CASCADE ON UPDATE CASCADE".
The relevant source code in SQL::Translator::Parser::DBIx::Class
appears to be this:
my $cascade;
for my $c (qw/delete update/) {
if (exists $rel_info->{attrs}{"on_$c"}) {
if ($fk_constraint) {
$cascade->{$c} = $rel_info->{attrs}{"on_$c"};
}
else {
carp "SQLT attribute 'on_$c' was supplied for relationship
'$moniker/$rel', which does not appear to be a foreign constraint. "
. "If you are sure that SQLT must generate a constraint
for this relationship, add 'is_foreign_key_constraint => 1' to the
attributes.\n";
}
}
elsif (defined $otherrelationship and
$otherrelationship->{attrs}{$c eq 'update' ? 'cascade_copy' :
'cascade_delete'}) {
$cascade->{$c} = 'CASCADE';
}
}
It looks like the user needs to use on_update and on_delete to specify
exactly what they want the constraint to do, rather than supplying a
true/false value to cascade_update/cascade_delete (which look as
though they might be deprecated?). This worked for me - I defined {
on_delete => 'NO ACTION', on_update => 'NO ACTION' } in the relevant
belongs_to relation and got the expected SQL.
I'm guessing this is just a case of slightly outdated doc in
DBIx::Class::Relationship - can someone in the know confirm either way
please? Happy to patch if required.
_______________________________________________
List:
http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-classIRC: irc.perl.org#dbix-class
SVN:
http://dev.catalyst.perl.org/repos/bast/DBIx-Class/Searchable Archive:
http://www.grokbase.com/group/dbix-class@...
