Tuesday, August 10, 2010

Cascading deletes in ColdFusion ORM

I started the delete functionality of my application today, for which I am using ColdFusion ORM, and I noticed puzzling behavior when deleting a parent object.  This example will use the following database design:


The relationship between a Story and its Tasks is one to many.  Task.storyid is a foreign key that cannot be NULL, as a Task cannot exist without its associated parent Story.  The ColdFusion classes would look like this:

Story.cfc
<cfcomponent displayname="Story" hint="I am the Story class." persistent="true">
  
  <!--- properties --->
  <cfproperty name="id" fieldtype="id" type="string" generator="uuid" unsavedvalue="-1" length="32" />
  <cfproperty name="name" type="string" />
  <cfproperty name="tasks" singularname="task"
          fieldtype="one-to-many" type="array" cfc="Task"
          fkcolumn="storyid" cascade="delete-orphan" />
  <!--- constructor --->
  <cffunction name="init" returntype="Story" access="public" output="false" hint="Constructor">
    
    <cfscript>
      setID(-1);
    </cfscript>
    
    <cfreturn this />
  </cffunction>
  
</cfcomponent>
Task.cfc
<cfcomponent displayname="Task" hint="I am the Task class." persistent="true">
  
  <!--- properties --->
  <cfproperty name="id" fieldtype="id" type="string" generator="uuid" unsavedvalue="-1" length="32" />
  <cfproperty name="storyid" type="string" />
        <cfproperty name="summary" type="string" />
  <!--- constructor --->
  <cffunction name="init" returntype="Task" access="public" output="false" hint="Constructor">
    
    <cfscript>
      setID(-1);
    </cfscript>
    
    <cfreturn this />
  </cffunction>
  
</cfcomponent>
Story.cfc defines the one-to-many relationship to it's children on line 6 by defining the relationship type, specifying the cfc that defines the child object, and by providing the foreign key column.  The other important bit here is the cascade attribute.  All cascade options may be found here.  (Note: I also tried 'delete' for the cascade attribute, but it had the same effect.)

When we perform an entityDelete() on the parent Story, I expected the child to delete.  Instead, I received the following error:
Error 1048: Column 'storyid' cannot be null. Root cause :java.sql.BatchUpdateException: Column 'projectid' cannot be null
By looking at the hibernate logging I could see that the following was attempting to occur:
  1. Load all objects in the relationship
  2. Set the storyid to NULL
  3. Delete the parent object
  4. Delete the child object
This order of events doesn't make sense, since in a one-to-many relationship as we defined above, storyid could never be NULL as it would break our model (Tasks only exist within the context of a Story).

To get the delete to cascade properly, I had to remove the required attribute of the FK column constraint. Why is this bad?  Well, without the requiring a NOT NULL storyid in the Task table, we allow a developer to persist an orphaned Task (one that never belongs to a Story).  While application code should prevent this from happening, the database should also enforce this policy by defining a proper schema.

The question is do the ends justify the means?  It was easy to get the cascade to function properly by breaking the database design, but now the schema does not properly enforce model constraints (in the real project I actually had 9 FK constraints across 7 tables).  The alternative is to create a delete() method in the Story object which deletes the Task before deleting the Story.  I don't like that since it does not override entityDelete() and goes around ORM as it should be implemented.  Thoughts?

6 comments:

  1. Nic I think the problem is that you're trying to define the storyID foreign key column yourself. Hibernate will automatically create the foreign key on the other table. The only "id" you should really ever be defining is the primary key for the entities.

    ReplyDelete
  2. What happens if you set cascade to "all-delete-orphan"?

    Can't test it right now, but I seem to remember it working for me in a similar situation...

    ReplyDelete
  3. Did you ever get around this issue, just working my way through it now.

    ReplyDelete
  4. I believe setting inverse="true" on the 'one' side of the relationship fixed this. It's been quite a while!

    ReplyDelete
  5. Yes. Setting inverse= true on one side of the relationship where the PK exists will resolve it properly

    ReplyDelete
  6. i unable to delete comment, i m using this code to delete comment.







    can anybody tell me whats wrong in it?

    Thnaks..
    suleman

    ReplyDelete