Foreign key constraint check using JPA/Hibernate on a MySQL cluster

Question

I have just been informed that `MySQL` clusters (using NDB engine) do not support foreign key constraints across clusters. Using `JPA`/`Hibernate`, how would you enforce referential integrity if the database doesn't enforce it? One option is to make a call to the database to validate the data then if valid perform the `insert/update/delete` operation. I am a bit reluctant to go with this approach as there is a performance impact. Questions: How do you usually enforce referential integrity checks when using `MySQL` cluster with `JPA`/`Hibernate` if foreign key checks are not supported in the database. If a manual check is the only option, is there any way I can complete the manual check transaction with a single trip to the database? (Possibly as part of the `CRUD` operation)

Accepted Answer

I don't think you will be able to enforce the referential integrity using any Hibernate/JPA functionality. As eggyal mentioned, you'll need to use triggers to create "programatic" foreign keys in MySQL itself.

There's an article on the MySQL website on how to do it: http://dev.mysql.com/tech-resources/articles/mysql-enforcing-foreign-keys.html

Foreign key constraint check using JPA/Hibernate on a MySQL cluster

Solution

Problem