New and old ways to emulate CHECK constraints, DOMAIN

Correctness of data comes in different forms. One is referential integrity, also known as foreign keys. Another is CHECK constraints. While MySQL supports foreign keys, it doesn’t support CHECK constraints. Today we will look at three ways to emulate them:

  • triggers
  • views
  • generated columns (new in MySQL 5.7)

This is also relevant to another SQL feature: DOMAIN, which, in short, is a user-defined type composed of a base type (INT, CHAR, …), a default value, and a constraint on acceptable values – the latter being some simple sort of CHECK constraint. If we can emulate CHECK, we can have some functionality of DOMAIN.

Let’s take a table of cars, with a column supposed to contain registration numbers of cars in France. Those numbers have a defined syntax: two letters, a dash, three digits, a dash, two letters. Of course, my example could also apply to storing UUIDs, telephone numbers, IP addresses, zip codes… anything with a defined syntax.

What we want to do is to make MySQL prevent invalid data like:

Using a trigger

SIGNAL sends an error if the to-be-inserted number doesn’t match the regular expression of the required syntax. Results:

Very nice. However, while it prevents inserting an invalid value, it doesn’t prevent updating a valid value to an invalid one:

To fix that, we need an additional trigger, a BEFORE UPDATE one:

And now the UPDATE properly fails:

The trigger-based solution certainly works well. The trigger could even do more complex checks (like, checking that the number is related to something in another table, by using a subquery). However, to create the triggers, we had to:

  • write 10 lines of procedural code, two times
  • have the TRIGGER privilege.


Let’s drop the triggers, empty the table and look at a different solution. We create a view cars_checked over the cars table; this view has a WHERE clause to show only valid rows. It has WITH CHECK OPTION so any insert/update done against the view will be rejected if it would cause the new row to not appear in the view, i.e. if it would cause the new row to be invalid.

In a real setup, we would revoke write access to cars from users, as such access allows them to bypass our check; we would rather give them write access to cars_checked only.

Like triggers, this solution works perfectly and is versatile (it is possible to use a subquery in the WHERE clause of the view). But it requires:

  • having the CREATE VIEW privilege
  • revoking privileges from users and granting them other privileges
  • possibly modifying our applications to make them target cars_checked instead of cars.

Edit: instead of naming the view cars_checked, it’s also possible to name it cars, after renaming the base table cars to cars_data. Then there is no need to change privileges, nor to change existing DMLs. However there is need to change some administrative DDLs which do not work against views. Thanks to Jörg Brühe for suggesting this; he provides more details in the thoughts section further down.

Using a generated column

Let’s try a new method, available since MySQL 5.7:

We add a generated column number_validate, it’s of type CHAR(0) NOT NULL so it can only be the empty string and takes no space in the record. It’s virtual which is another reason why it takes no space. If the number is valid our new column evaluates to the empty string (”), which fits into the column’s type, so the insert/update succeeds; if the number is invalid our new column evaluates to NULL (there’s an implicit “ELSE NULL” in the CASE), which is rejected because of NOT NULL.

By the way, notice how my original insert into cars values(‘AS-229-ZT’) started failing: because my row has two columns now; so I have to either specify which column I’m inserting into (insert into cars (number) as above), or use default for the generated column:

Let’s compare our three solutions for emulating CHECK constraints:

  • the trigger/view solutions are powerful though complicated to deploy.
  • the generated-column solution is less powerful, as a generated column’s expression cannot contain any subquery; but it is easy to deploy, as it is attached directly to the table and takes just one line of SQL to create. For doing simple validity checks like the ones in my example, it is, in my opinion, the winner.

I hope some of these solutions will help you implement data validation in your MySQL tables. Thank you for using MySQL!

10 thoughts on “New and old ways to emulate CHECK constraints, DOMAIN

  1. Hi Guilhem!
    That is an interesting read, nice proposals – thank you!

    Regarding your second approach, “create view … with check option”:
    Wouldn’t it be easier to rename the base table, say to “cars_data”, and then to create a view “cars” with the check constraint?
    This way, no application or user privileges would need to change. The short downtime (between “rename table” and “create view”) should be acceptable.

    Also, the need for the “create view” privilege is a non-issue IMHO, when the goal is to improve consistency.


    1. Hello Jörg, long time talked to you!
      Thanks for the rename-table suggestion, it can indeed make things easier.
      I’d just add that some care is needed as any pre-existing SQL, which used to run against the base table “cars”, would now run against view “cars”, and while this is fine for INSERT/DELETE/UPDATE/SELECT, it’s not for some other statements; for example imagine there exists a daily job which does “ANALYZE TABLE cars”, it would now get:
      mysql> analyze table cars;
      | Table | Op | Msg_type | Msg_text |
      | | analyze | Error | ‘’ is not BASE TABLE |
      | | analyze | status | Operation failed |
      Another example:
      mysql> create table cars2 like cars;
      ERROR 1347 (HY000): ‘’ is not BASE TABLE

      1. Hi Guilhem!
        Yes, it is nice to get in contact again.
        Sure, administrative applications that need the base table will need changing – but who cares?
        They are the minority, compared to operational applications, and they will not malfunction but rather produce a nice error message.
        Even if they fail, operation will continue.
        The “rename table” avoids all needs to change the “normal” code (which may be deployed on many client machines) and allows a near-instantaneous change, IMO that is what matters.
        Take care!

  2. Pingback: URL

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter *