In this blog post, we’ll look at the MariaDB 10.2 CHECK and DEFAULT clauses.
MariaDB 10.2 includes some long-awaited features. In this blog, we are going to discuss the improvements to some table definitions: the DEFAULT clause and the CHECK constraints. These clauses describe columns default values and rules for data validation.
Note that MariaDB 10.2 is still in the alpha stage. This article describes the current state of these features, which could change before MariaDB 10.2 becomes GA.
The DEFAULT Clause
The DEFAULT clause has always been supported in MariaDB/MySQL, but traditionally it only accepted literal values (like “hello world” or “2”). MariaDB 10.2 removes this limitation, so DEFAULT can now accept most SQL expressions. For example:
- fiscal_year SMALLINT DEFAULT (YEAR(NOW())).
- valid_until DATE DEFAULT (NOW() + INTERVAL 1 YEAR).
- owner VARCHAR(100) DEFAULT (USER()).
Additionally, MariaDB 10.2 allows you to set a DEFAULT value for the TEXT and BLOB columns. This was not possible in previous versions. While this might look like a small detail, it can be hard to add a column to an existing table that is used by production applications, if it cannot have a default value.
The DEFAULT clause has some limitations. For example, it cannot contain a subquery or a stored function. An apparently strange limitation is that we can mention another column in DEFAULT only if it comes first in the CREATE TABLE command.
Note that DEFAULT can make use of non-deterministic functions even if the binary log uses the STATEMENT format. In this case, default non-deterministic values will be logged in the ROW format.
CHECK Constraints
CHECK constraints are SQL expressions that are checked when a row is inserted or updated. If this expression result is false (0, empty string, empty date) or NULL, the statement will fail with an error. The error message states which CHECK failed in a way that is quite easy to parse:
Some example of CHECK constraints:
- CONSTRAINT non_empty_name CHECK (CHAR_LENGTH(name) > 0).
- CONSTRAINT consistent_dates CHECK (birth_date IS NULL OR death_date IS NULL OR birth_date < death_date).
- CONSTRAINT past_date CHECK (birth_date < NOW()).
A possible trick is checking that a column is different from its default value. This forces users to assign values explicitly.
CHECK constraints cannot be added or altered. It is only possible to drop them. This is an important limitation for production servers.
Another limitation is that CHECK metadata are not accessible via the INFORMATION_SCHEMA. The only way to find out if a table has CHECK clauses is parsing the output of SHOW CREATE TABLE.
The exact behavior of CHECK constraints in a replication environment depends on the master binary log format. If it is STATEMENT, the slaves will apply CHECK constraints to events received from the master. If it is ROW, only the master will need to apply constraints, because failed statements will not be replicated.
Thus, in all cases, we recommend having identical constraints on master and slaves, and only using deterministic constraints.
Performance
While I didn’t run a professional benchmark, I can say that both DEFAULT and CHECK clauses don’t have a noticeable impact on a simple test where we insert 1 million rows (on my local machine).
However, these clauses evaluate an SQL expression each time a row is inserted or updated. The overhead is at least equal to the SQL expression performance. If high-performing writes are important, you will probably not want to use complex data validation.
To check how fast an expression is, we can use the BENCHMARK() function:
In this example, we executed the specified expressions 10 million times. BENCHMARK() always returns 0, but what we want to check is the execution time. We can see for example that evaluating MD5(‘hello world’) takes less than 0.000002 seconds. In some cases, we may want to retry the same expressions with different parameters (longer strings, higher numbers, etc.) to check if the execution times varies.
Unfortunately, we don’t have a status variable which tells us how many times MariaDB evaluated CHECK clauses. If our workload performs many writes, that variable could help us to find out if CHECK constraints are slowing down inserts. Maybe the MariaDB team can take this as a suggestion for the future.
