[Dbix-class] Deployment without cascading updates/deletes

[Peter Rabbitson]

On Wed, Oct 14, 2009 at 05:38:53PM +0100, Will Hawes wrote:
> 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".
> 
> <snip>
>
> 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.

Yes, you are entirely correct. A full explanation of what actually
happens can be found here[1]. A patch to other parts of the docs
building on top of this info is very appreciated. Please patch against[2]

Cheers!

[1] http://search.cpan.org/~ribasushi/DBIx-Class-0.08112/lib/DBIx/Class/Relationship/Base.pm#on_delete
[2] http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/trunk/