ON DELETE CASCADE option not in generated foreign-key

jconstantin · **Posted:** Wed Apr 23, 2008 11:49 am

I want the foreign-key constraint to have the "on delete cascade" option in the generate DDL. I believe I have followed the documentation correctly but it never generated the "on delete cascade" option for the generated foreign-key constraint. I know this must be something simple I am doing wrong but I don't see it. Please help.

I have a simple example Parent and Child. The generated SQL contains the foreign-key but not the "on delete cascade".

What am I doing wrong?
Any Help is greatly appreciated.

Thanks.

Hibernate version:
Hibernate 3.2
JDK 1.5.0_15

Mapping documents:

Parent:

Code:

      
       <id name="uid" type="java.lang.Long">
            <column name="uid" />
            <generator class="identity" />
        </id>
        <set name="children" inverse="true" cascade="all,delete-orphan" >
      <key column="parent_uid" on-delete="cascade" /> 
                <one-to-many class="Child" />
        </set>

Child:

Code:

        <id name="uid" type="java.lang.Long">
            <column name="uid" />
            <generator class="identity" />
        </id>
        <many-to-one name="parent" foreign-key="ParentFK"
                cascade="all"
                class="com.clareity.textpass.db.api.Parent" fetch="select" lazy="false" column="parent_uid" not-null="true" />

Development Environment

Windows XP, Ant .17

Ant Task

Code:

   
       <target name="ddl" depends="init">
        <taskdef name="hibernatetool" 
              classname="org.hibernate.tool.ant.HibernateToolTask" 
               classpathref="toolslib" />
       <hibernatetool destdir="src" >
        <configuration configurationfile="./conf/hibernate.cfg.xml" />
        <hbm2ddl export="false" 
              outputfilename="../conf/${ddl.name}"
              drop="true"
              create="true"
              delimiter=";"
              format="true"
              haltonerror="true"
              />
       </hibernatetool>
   </target>

Name and version of the database you are using:

MySql Server 5.0

The generated SQL (show_sql=true):

Code:

 alter table ctp_textpass.ctp_children
     drop
     foreign key ChildFK;

 drop table if exists ctp_textpass.ctp_children;

 drop table if exists ctp_textpass.ctp_parents;

 create table ctp_textpass.ctp_children (
     uid bigint not null auto_increment,
     parent_uid bigint not null,
     primary key (uid)
 );

 create table ctp_textpass.ctp_parents (
     uid bigint not null auto_increment,
     primary key (uid)
 );

 alter table ctp_textpass.ctp_children
     add index ChildFK (parent_uid),
     add constraint ChildFK
     foreign key (parent_uid)
     references ctp_textpass.ctp_parents (uid);

jconstantin · **Posted:** Wed Apr 23, 2008 3:00 pm

Some progress made:

I changed the dialect to SQLServerDialect and it works: notice the constraint with the "on delete cascade". Does any one know if this has been addressed for the MySQLDialect?

The SQL Server generated DDL:

Code:

alter table ctp_test.dbo.children
    drop constraint Child2Parent;

drop table ctp_test.dbo.children;

drop table ctp_test.dbo.parents;

create table ctp_test.dbo.children (
    uid numeric(19,0) identity not null,
    parent_uid numeric(19,0) null,
    primary key (uid)
);

create table ctp_test.dbo.parents (
    uid numeric(19,0) identity not null,
    primary key (uid)
);

alter table ctp_test.dbo.children
    add constraint Child2Parent
    foreign key (parent_uid)
    references ctp_test.dbo.parents
    on delete cascade;

Sukirtha · **Posted:** Thu Apr 24, 2008 11:46 pm

try using org.hibernate.dialect.MySQL5Dialect

jconstantin · **Posted:** Fri Apr 25, 2008 8:35 am

Thanks, I tried that but the outcome did not change.

Sukirtha · **Posted:** Mon Apr 28, 2008 4:54 am

It doesn't work for me too. Any idea why it does not work??

Sukirtha · **Posted:** Mon Apr 28, 2008 7:06 am

Hi,
I found that the MySQL related dialects do not support on cascade delete currently. I am not sure if it is a bug or feature. If someone can clarify on that, it would be great. I feel its a bug.

jconstantin, for now you can create a custom dialect and override the supportsCascadeDelete() method and return a boolean value to true. That should solve your problem.

jconstantin · **Posted:** Tue Apr 29, 2008 1:50 pm

Sukirtha,

I created a custom dialect.

Code:

public class TestMySql5Dialect extends MySQL5Dialect {

    public boolean supportsCascadeDelete(){
       return true;
    }
}

I change the hibernate.cfg.xml to use the custom dialect.

Code:

        <property name="hibernate.dialect">com.clareity.scrub.util.TestMySql5Dialect</property>

I added this custom dialect to my toolslib path in build.xml
The class is compiled to build.classes.dir so it should be in the
classpath.

Code:

    <path id="toolslib">
       <pathelement location="${build.classes.dir}"/>
       <fileset dir="${build.lib.dir}">
          <include name="*.jar"/>
      </fileset>
      <pathelement location="./src"/>
      <pathelement location="./conf"/>
    </path>

My build.xml "ddl" target

Code:

<target name="ddl" depends="init">
       <taskdef name="hibernatetool" 
              classname="org.hibernate.tool.ant.HibernateToolTask" 
               classpathref="toolslib" />
       <hibernatetool destdir="src" >
        <configuration configurationfile="./conf/hibernate.cfg.xml" />
        <hbm2ddl export="false" 
              outputfilename="../conf/${ddl.name}"
              drop="true"
              create="true"
              delimiter=";"
              format="true"
              haltonerror="true"
              />
       </hibernatetool>
</target>

The result:

Code:

[hibernatetool] org.hibernate.HibernateException: Dialect class not found: com.clareity.scrub.util.TestMySql5Dialect

This all seems very simple, but it does not work. Is there something special about using a custom dialect? I will keep trying.

Thanks for the suggestions.

jconstantin

jconstantin · **Posted:** Tue Apr 29, 2008 2:01 pm

Sukirtha,

It is working now. My Custom Dialect class was incomplete. I forgot the empty constructor and the call to super();

Code:

public class TestMySql5Dialect extends MySQL5Dialect {

   public TestMySql5Dialect(){
      super();
   }
   
    public boolean supportsCascadeDelete(){
       System.out.println("--- supportsCascadeDelete() called.");
       return true;
    }
}

Thanks for the help with this.

Sukirtha · **Posted:** Tue Apr 29, 2008 11:30 pm

Good. Happy I could help you... don forget to rate ;)

But still I would like to know if the non availablity of on delete cascade in MySQL is a feature or bug. Any One???

suke · **Joined:** Thu Nov 17, 2005 8:55 pm **Posts:** 3

You can use org.hibernate.dialect.MySQL5InnoDBDialect instead.