Follow @vlad_mihalcea Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!

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.

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).

Insert details about how the information is going to be processed DOWNLOAD NOW