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-class IRC: irc.perl.org#dbix-class SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/ Searchable Archive: http://www.grokbase.com/group/dbix-class@lists.scsys.co.uk