Cann't delete childs rows in manytomany Associations

Question

I have a ManyToMany Association that does not allow me to delete a parent when the child item are linked to other entities... Anybody has the solution please ?

Parent Model :

/*
 * Copyright 2012 Steve Chaloner
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package models;

import be.objectify.deadbolt.java.models.Permission;
import be.objectify.deadbolt.java.models.Role;
import be.objectify.deadbolt.java.models.Subject;
import com.avaje.ebean.Model;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.ManyToMany;
import java.util.List;

import models.utils.AppException;
import models.utils.Hash;
import play.data.format.*;
import play.data.validation.*;
import com.avaje.ebean.*;
import com.avaje.ebean.annotation.PrivateOwned;
import com.avaje.ebean.Query;
import play.db.ebean.*;

import javax.persistence.*;

import java.util.Date;
import java.util.HashSet;
import java.util.Set;

import javax.persistence.CascadeType;
import javax.persistence.OneToMany;
import org.hibernate.annotations.OnDelete;
import org.hibernate.annotations.OnDeleteAction;
import java.util.ArrayList;

/**
 * @author Steve Chaloner ([email protected])
 */
@Entity
@Table(name = "authorised_user")
public class AuthorisedUser extends Model implements Subject
{
    @Id
    public Long id;

    //@Constraints.Required
    //@Formats.NonEmpty
    //@Column(unique = true)
    public String userName;

    @Constraints.Required
    @Formats.NonEmpty
    @Column(unique = true)
    public String email;

    public String fullname;

    public String profession;

    public String language;  

    @Constraints.Required
    @Formats.NonEmpty
    public String password_hash;

    @Formats.DateTime(pattern = "yyyy-MM-dd HH:mm:ss")
    public Date createdt;

    public String createby;

    @Formats.DateTime(pattern = "yyyy-MM-dd HH:mm:ss")
    public Date moddt;

    public String modby;

    @Formats.NonEmpty
    public Boolean validated = false;

    public String imagepath;

    @ManyToOne
    public Site site;            

    @PrivateOwned
    @OneToMany(cascade = CascadeType.ALL)
    public List<User_Sites> relationSitesList = new ArrayList<>();   

    @PrivateOwned
    @OneToMany(cascade = CascadeType.ALL)
    public List<Image> relationImagesList = new ArrayList<>(); 

    @ManyToMany(cascade=CascadeType.ALL)
    public List<SecurityRole> roles;

    @ManyToMany
    public List<UserPermission> permissions;

    public static final Finder<Long, AuthorisedUser> find = new Finder<>(Long.class,
                                                                         AuthorisedUser.class);

    @Override
    public List<? extends Role> getRoles()
    {
        return roles;
    }

    @Override
    public List<? extends Permission> getPermissions()
    {
        return permissions;
    }

    @Override
    public String getIdentifier()
    {
        return userName;
    }

    public static AuthorisedUser findByUserName(String userName)
    {
        return find.where()
                   .eq("userName",
                       userName)
                   .findUnique();
    }


    public static AuthorisedUser findByEmail(String email) {
        return find.where().eq("email", email).findUnique();
    }


    public static AuthorisedUser findByFullname(String fullname) {
        return find.where().eq("fullname", fullname).findUnique();
    }


    public static AuthorisedUser authenticate(String email, String clearPassword) throws AppException {

        // get the user with email only to keep the salt password
        AuthorisedUser user = find.where().eq("email", email).findUnique();
        if (user != null) {
            // get the hash password from the salt + clear password
            if (Hash.checkPassword(clearPassword, user.password_hash)) {
                return user;
            }
        }
        return null;
    }


    public void changePassword(String password) throws AppException {
        this.password_hash = Hash.createPassword(password);
        this.save();
    }


    public static boolean confirm(AuthorisedUser user) throws AppException {
        if (user == null) {
            return false;
        }

        user.validated = true;
        user.save();
        return true;
    }

    public static PagedList<AuthorisedUser> page()                           

    {    


        RawSql rawSql = RawSqlBuilder.parse(" SELECT "  

                                            +"user.id, "
                                            +"user.user_name, "                                            
                                            +"user.email, "
                                            +"user.fullname, "
                                            +"user.profession, "     
                                            +"user.password_hash, "                                      
                                            +"user.createdt, "                                      
                                            +"user.createby, "                                            
                                            +"user.moddt, "
                                            +"user.modby, "                                            
                                            +"user.validated "

                                            +"from authorised_user as user "
                                            +" ")


                  .columnMapping("user.id", "id")
                  .columnMapping("user.user_name", "userName")                  
                  .columnMapping("user.email", "email")
                  .columnMapping("user.fullname", "fullname")
                  .columnMapping("user.profession", "profession")                                    
                  .columnMapping("user.password_hash", "password_hash")
                  .columnMapping("user.createdt", "createdt")
                  .columnMapping("user.createby", "createby")                  
                  .columnMapping("user.moddt", "moddt")
                  .columnMapping("user.modby", "modby")                                    
                  .columnMapping("user.validated", "validated")                                                                    

                  .create();

                  Query<AuthorisedUser> query = Ebean.find(AuthorisedUser.class);
                  query.setRawSql(rawSql);



                  PagedList<AuthorisedUser> result = query.findPagedList(0, 0);
                  return result;


    }                        
}

Child Model

package models;
import be.objectify.deadbolt.java.models.Role;
import com.avaje.ebean.Model;
import javax.persistence.Entity;
import javax.persistence.Id;
import models.utils.AppException;
import models.utils.Hash;
import play.data.format.Formats;
import play.data.validation.Constraints;
import com.avaje.ebean.*;
import com.avaje.ebean.annotation.PrivateOwned;
import com.avaje.ebean.Query;
import play.db.ebean.*;
import javax.persistence.Column;
import javax.persistence.Table;
import javax.persistence.*;
import java.util.Date;
import java.util.Map;
import java.util.List;
import java.util.HashMap;
import javax.persistence.CascadeType;
import javax.persistence.OneToMany;
import java.util.ArrayList;
/**
 * @author Steve Chaloner ([email protected])
 */
@Entity
@Table(name = "security_role")
public class SecurityRole extends Model implements Role
{
    @Id
    public Long id;
    @Constraints.Required
    @Formats.NonEmpty
    @Column(unique = true)
    public String name;
    @Formats.DateTime(pattern = "yyyy-MM-dd HH:mm:ss")
    public Date createdt;
    public String createby;
    @Formats.DateTime(pattern = "yyyy-MM-dd HH:mm:ss")
    public Date moddt;
    public String modby;
    public static final Finder<Long, SecurityRole> find = new Finder<>(Long.class,
                                                                       SecurityRole.class);
    public Long getId()
    {
        return id;
    }
    public String getName()
    {
        return name;
    }
    public static SecurityRole findByName(String name)
    {
        return find.where()
                   .eq("name",
                       name)
                   .findUnique();
    }
    public static SecurityRole findById(Long id) {
        return find.where().eq("id", id).findUnique();
    }

}**

Here is the SQL creation :

create table authorised_user (
  id                            bigint auto_increment not null,
  user_name                     varchar(255),
  email                         varchar(255),
  fullname                      varchar(255),
  profession                    varchar(255),
  language                      varchar(255),
  password_hash                 varchar(255),
  createdt                      datetime(6),
  createby                      varchar(255),
  moddt                         datetime(6),
  modby                         varchar(255),
  validated                     tinyint(1) default 0,
  imagepath                     varchar(255),
  site_id                       bigint,
  constraint uq_authorised_user_email unique (email),
  constraint pk_authorised_user primary key (id)
);

create table security_role (
  id                            bigint auto_increment not null,
  name                          varchar(255),
  createdt                      datetime(6),
  createby                      varchar(255),
  moddt                         datetime(6),
  modby                         varchar(255),
  constraint uq_security_role_name unique (name),
  constraint pk_security_role primary key (id)
);

create table authorised_user_security_role (
  authorised_user_id            bigint not null,
  security_role_id              bigint not null,
  constraint pk_authorised_user_security_role primary key (authorised_user_id,security_role_id)
);

alter table authorised_user add constraint fk_authorised_user_site_id foreign key (site_id) references site (id) on delete restrict on update restrict;
create index ix_authorised_user_site_id on authorised_user (site_id);

alter table authorised_user_security_role add constraint fk_authorised_user_security_role_authorised_user foreign key (authorised_user_id) references authorised_user (id) on delete restrict on update restrict;
create index ix_authorised_user_security_role_authorised_user on authorised_user_security_role (authorised_user_id);

alter table authorised_user_security_role add constraint fk_authorised_user_security_role_security_role foreign key (security_role_id) references security_role (id) on delete restrict on update restrict;
create index ix_authorised_user_security_role_security_role on authorised_user_security_role (security_role_id);

Those models are taken from deadbolt security & roles.

If someone has a suggestion of answers do not hesitate please.

Answer 1

Thank you c4k, you were right, I changed my SQL to this and it works pretty fine, thank you really really and so much: (however, i can't ++your_answer_as_useful because i am newer ...sorry)

alter table authorised_user_security_role add constraint fk_authorised_user_security_role_security_role foreign key (security_role_id) references security_role (id) on delete cascade on update restrict;

create index ix_authorised_user_security_role_security_role on authorised_user_security_role (security_role_id); 

Answer 2

Check the MySQL documentation for on delete.

Be careful, you need to be sure that it is what you want to do. You might lose unexpected data if you don't think about it before applying cascade.

So changing on delete restrict to on delete cascade in you SQL script will resolve your problem.