JPA - inheritance - foreign key constraint fails

Question

I have two classes: Department and Employee, and a one-to-many-relationship between them. One Department can have multiple Employees and one Employee belongs to one Department. When I want to persist them into a (MySQL-)database, using JPA 2.1 RI (EclipseLink 2.5.0), it works fine. But, when I make them subclasses (Department extends SuperClassOne and Employee extends SuperClassTwo) for some reason and then I want to persist them into a database, I got an exception. It seems that the order of insert-statements sent to the RDBMS is not good. What am I doing wrong?

SQL DDL:

CREATE TABLE SuperClassOne (
    id INTEGER NOT NULL AUTO_INCREMENT,
    type VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

CREATE TABLE Department (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES SuperClassOne (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

CREATE TABLE SuperClassTwo (
    id INTEGER NOT NULL AUTO_INCREMENT,
    type VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

CREATE TABLE Employee (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    departmentId INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES SuperClassTwo (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    FOREIGN KEY (departmentId) REFERENCES Department (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

class Department:

@Entity
@Table(name = "Department")
public class Department extends SuperClassOne {

//    @Id
//    @GeneratedValue(strategy = GenerationType.IDENTITY)
//    @Column(name = "id")
//    private Integer id;

    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "department", cascade = CascadeType.PERSIST)
    private List<Employee> employees = new ArrayList<>();

    public Department() {
        super();
    }

//    public Integer getId() {
//        return id;
//    }
//
//    public void setId(Integer id) {
//        this.id = id;
//    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }

    public void addEmployee(Employee employee) {
        employee.setDepartment(this);
        employees.add(employee);
    }
}

class Employee:

@Entity
@Table(name = "Employee")
public class Employee extends SuperClassTwo {

    //    @Id
    //    @GeneratedValue(strategy = GenerationType.IDENTITY)
    //    @Column(name = "id")
    //    private Integer id;

    @Column(name = "name")
    private String name;

    @ManyToOne
    @JoinColumn(name = "departmentId")
    private Department department;

    public Employee() {
        super();
    }

//    public Integer getId() {
//        return id;
//    }

//    public void setId(Integer id) {
//        this.id = id;
//    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }
}

class SuperClassOne:

@Entity
@Table(name = "SuperClassOne")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "type")
// @DiscriminatorValue(value = "SuperClassOne")
public class SuperClassOne {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Integer id;

    @Column(name = "type", nullable = false)
    private String type;

    public SuperClassOne() {
        super();
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }
}

class SuperclassTwo:

@Entity
@Table(name = "SuperClassTwo")
@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "type")
// @DiscriminatorValue(value = "SuperclassTwo")
public class SuperClassTwo {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", nullable = false)
    private Integer id;

    @Column(name = "type", nullable = false)
    private String type;

    public SuperclassTwo() {
        super();
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getType() {
        return type;
    }

    public void setType(String type) {
        this.type = type;
    }
}

class Main:

public static void main(String[] args) {
    EntityManagerFactory emf = Persistence.createEntityManagerFactory("test-PU");
    EntityManager em = emf.createEntityManager();

    // ----------

    Department department = new Department();
    department.setName("Bla");

    Employee employee = new Employee();
    employee.setName("Marc Beckers");

    department.addEmployee(employee);

    EntityTransaction et = em.getTransaction();
    et.begin();
    em.persist(department);
    // em.persist(employee);
    et.commit();

    // ----------

    em.close();
    emf.close();
}

Error:

[EL Warning]: 2014-01-27 13:13:01.546--UnitOfWork(1647452011)--Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails (`test`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`departmentId`) REFERENCES `department` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
Error Code: 1452
Call: INSERT INTO Employee (name, departmentId, id) VALUES (?, ?, ?)
    bind => [3 parameters bound]
Query: InsertObjectQuery(nl.bla.persistence.Employee@4c32cdeb)

Answer 1

This is the complete solution:

SQL DDL:

CREATE TABLE SuperClassOne (
    id INTEGER NOT NULL AUTO_INCREMENT,
    type VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

CREATE TABLE SuperClassTwo (
    id INTEGER NOT NULL AUTO_INCREMENT,
    type VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

CREATE TABLE Department (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES SuperClassOne (id) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

CREATE TABLE Employee (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    departmentId INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES SuperClassTwo (id) ON DELETE RESTRICT ON UPDATE RESTRICT,
    FOREIGN KEY (departmentId) REFERENCES Department (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

class Department:

@Entity
@Table(name = "Department")
@Customizer(InheritanceCustomizer.class)
public class Department extends SuperClassOne {

    @Column(name = "name")
    private String name;

    @OneToMany(mappedBy = "department", cascade = CascadeType.ALL)
    private List<Employee> employees = new ArrayList<>();

    public Department() {
        super();
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Employee> getEmployees() {
        return employees;
    }

    public void setEmployees(List<Employee> employees) {
        this.employees = employees;
    }

    public void addEmployee(Employee employee) {
        employee.setDepartment(this);
        employees.add(employee);
    }
}

class Employee:

@Entity
@Table(name = "Employee")
public class Employee extends SuperClassTwo {

    @Column(name = "name")
    private String name;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "departmentId")
    private Department department;

    public Employee() {
        super();
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Department getDepartment() {
        return department;
    }

    public void setDepartment(Department department) {
        this.department = department;
    }
}

class InheritanceCustomizer:

public class InheritanceCustomizer implements DescriptorCustomizer{

    @Override
    public void customize(ClassDescriptor descriptor) throws Exception {
        descriptor.setHasMultipleTableConstraintDependecy(true);
    }
}

Answer 2

It seems that it is a "bug" in EclipseLink. Although some argue that it is, strictly speaking, not a bug, but just implemented as specified. See also: https://bugs.eclipse.org/bugs/show_bug.cgi?id=333100. If I change the SQL DDL, so that the foreign key departmentId references the primary key id of the superclass SuperClassOne instead of the subclass Department, it all works fine.

old situation:

CREATE TABLE Employee (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    departmentId INTEGER NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (departmentId) REFERENCES Department (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

new situation:

CREATE TABLE Employee (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    departmentId INTEGER NOT NULL,
    PRIMARY KEY (id),
    -- FOREIGN KEY (departmentId) REFERENCES Department (id) ON DELETE CASCADE ON UPDATE CASCADE
    FOREIGN KEY (departmentId) REFERENCES SuperClassOne (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARACTER SET = utf8 DEFAULT COLLATE = utf8_bin;

In my opinion, it may not be a bug, but in any case a limitation, because now I can also put a primary key id of another subclass of SuperClassOne in the foreign key departmentId. So I have to check this manually by code :-( So I hope EclipseLink will change this behavior.

Answer 3

You can only omit the referencedColumnName in the join column when the target table has the same column name as the reference table.

So in your entity employee you should have:

@ManyToOne
@JoinColumn(name = "departmentId", referencedColumnName="id")
private Department department;

Maybe you renamed the columns between tests, because it shouldn't have worked the first time also.