0

As I understand from the MySQL documentation and from this question, it is NOT POSSIBLE to use deferred constraint in MySQL.

So, my question is; with below entity model (a uni-directional relation) in Hibernate, I want to swap two a collection of Package between two Product entities within a Transaction.

 public class Product{
 @OneToMany(fetch = FetchType.LAZY)
    @JoinTable(
            name="product_selected_packages",
            joinColumns = {@JoinColumn(name = "product")},
            inverseJoinColumns = {@JoinColumn(name="packages",unique = true)},
            uniqueConstraints = {@UniqueConstraint(name = "UK_package",columnNames = {"packages"})}
    )
    private List<Package> packages;

}


public class Package {

    @Column(unique = true)
    private String code;
}

But when I write this type of code below (this is all done within a @Transactional method), 

List<Package> product1Packages = new ArrayList<>(product1.getPackages()); // save product1 packages into memory

product1.getPackages().clear(); // clear all product1 packages
session.flush(); // flush session in order to sync db with hibernate 

product2.getPackages().addAll(product1Packages);

Of course there is a more complex logic behind it but in a general way, Hibernate produces below SQL(which makes sense)

START TRANSACTION;

DELETE FROM product_selected_packages WHERE product = :SOME_ID; 

INSERT INTO product_selected_packages(product,packages) VALUES (1,1) , (1,2) , (1,3) ...;

ROLLBACK; -- OR COMMIT

But due to MySQL's limitations on constraints which is stated in the documentation; above statement throws an error.

Duplicate entry '1621' for key 'UK_unique_index_constraint'

Because documentation states;

MySQL checks foreign key constraints immediately; the check is not deferred to transaction commit.

Long story short, how can I create a work-around method for this type of situation without losing the Transaction ?

paroxit
  • 627
  • 2
  • 12
  • 30
  • Why exactly does it throw an error? All relevant foreign keys should be fine, shouldn't they? Or do you have something else referring to `produtct_selected_packages`? – Jens Schauder Jun 15 '20 at 06:25
  • I've added the exception in the question – paroxit Jun 15 '20 at 14:15
  • So where is this `UK_unique_index_constraint` come from? And on which table and columns is it? – Jens Schauder Jun 15 '20 at 14:48
  • Because it's a uni-directional relationship, this Unique Constraint lies on the "product_selected_packages" table which is created by the hibernate automatically (combining two tables). Has two columns, product (FK to Product) and packages (FK to Package). – paroxit Jun 15 '20 at 15:14
  • I don't think so. It says "Duplicate entry '1621'. This seems to indicate that the constraint has only one column. – Jens Schauder Jun 15 '20 at 15:19
  • I'm sorry, you're right. Contraint only works for one column which is "packages" field. But that's the correct behaviour, that's what we want. What is wrong is that , row that points to '1621' package with some product should be removed from db before inserting into another product. – paroxit Jun 16 '20 at 11:01
  • So Hibernate is not issuing the statements given in the question? What statements is it issuing? – Jens Schauder Jun 16 '20 at 11:05
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/216048/discussion-between-jens-schauder-and-paroxit). – Jens Schauder Jun 16 '20 at 11:05

0 Answers0