MySQL 8 support for custom SQL CHECK constraints

(Last Updated On: July 3, 2019)

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.

Custom SQL CHECK constraints

As I explained inthis 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 outthis 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 ).