MySQL 8 support for custom SQL CHECK constraints
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
Introduction
In this article, we are going to test the MySQL 8 implementation of custom SQL CHECK constraints. Although the CHECK clause is a standard SQL feature, prior to MySQL 8.0.16, the clause was parsed and ignored, so this functionality could only be emulated via BEFORE INSERT and UPDATE triggers.
How to test the MySQL 8 implementation of custom SQL CHECK constraints@vlad_mihalcea https://t.co/4rDn4J6K5p pic.twitter.com/kPWL6aFIZ2
— Java (@java) July 5, 2019
Custom SQL CHECK constraints
As I explained in this article, custom SQL CHECK constraints are very useful to ensure non-nullability constraints for JPA entity subclass-specific attributes when using the SINGLE TABLE JPA inheritance strategy.
To understand the problem, consider we have the following domain model:
When using the SINGLE TABLE inheritance strategy, the base class and all subclasses share the same database table:
However, if the subclass-specific attributes like the content
and validUntil
attributes of the Post
and Announcement
entities are required to be non-nullable, we cannot just add the NOT NULL
constraint at the SQL column level as, otherwise, we won’t be able to add a post
record as the valid_until
column NOT NULL
constraint will fail.
That’s exactly where SQL custom CHECK constraints can help us. Therefore, we could add the following two CHECK constraints:
ALTER TABLE topic ADD CONSTRAINT post_content_check CHECK ( CASE WHEN DTYPE = 'Post' THEN CASE WHEN content IS NOT NULL THEN 1 ELSE 0 END ELSE 1 END = 1 ) ALTER TABLE topic ADD CONSTRAINT announcement_validUntil_check CHECK ( CASE WHEN DTYPE = 'Announcement' THEN CASE WHEN validUntil IS NOT NULL THEN 1 ELSE 0 END ELSE 1 END = 1 )
MySQL 8 support for custom SQL CHECK constraints
Now, if we try to persist a Post
entity without a valid content
property:
entityManager.persist(new Post());
MySQL 8.0.16 is going to throw the following ConstraintViolationException
:
INSERT INTO topic ( board_id, createdOn, owner, title, content, DTYPE, id ) VALUES ( NULL(BIGINT), '2019-07-03 10:40:03.933', NULL(VARCHAR), NULL(VARCHAR), NULL(VARCHAR), 'Post', 4 ) -- SQL Error: 1644, SQLState: 45000 -- Post content cannot be NULL
The same happens if we try to update an existing Post
entity and set the content
attribute to null
:
Post post = entityManager .createQuery( "select p " + "from Post p " + "where p.content = :content", Post.class) .setParameter("content", "Best practices") .getSingleResult(); post.setContent(null);
MySQL 8.0.16 is going to throw a ConstraintViolationException
because the custom post_content_check
CHECK constraint fails to validate the UPDATE statement:
UPDATE topic SET board_id = 1, createdOn = '2019-07-03 10:45:53.581', owner = 'John Doe', title = 'Inheritance', content = NULL(VARCHAR) WHERE id = 2 -- SQL Error: 1644, SQLState: 45000 -- Post content cannot be NULL
Awesome, right?
If you want to see what you had to do to emulate the CHECK
constraint on MySQL prior to 8.0.16. then check out this article.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
If you are still using an older version of MySQL, you should definitely consider upgrading to the 8.0 version as there are many features that have been added to MySQL in this major version increment (e.g. Window Functions, SKIP LOCKED and NOWAIT, CTE or Recursive CTE).
