Our database tools team is pleased to announce the release of our dbForge MySQL products with a CHECK constraint support. We are looking forward to seeing our customers benefit from it in their everyday work.
The fact that MySQL doesn’t support CHECK Constraints syntax caused lots of inconveniences to the developers and DBAs. That deviation from SQL standard complicated maintaining data integrity and assuring data quality. Introducing a CHECK constraint feature in the 8.0.16 edition was one of the most requested and long-awaited features for MySQL. In our turn, at Devart, we aim to bring the latest innovations to our customers to keep them up to date with the new technologies.
What is a CHECK constraint?
A CHECK constraint is a type of integrity constraint in SQL, it allows users to specify a condition on each row in a table. It is used to limit the value range that can be placed in a column. The constraint must be a predicate and can refer to a single column, or multiple columns of a table. Depending on the presence of NULLs, the result of the predicate can be:
• True
• False
• Unknown
In case the predicate evaluates to UNKNOWN, the constraint is not violated and the row can be inserted or updated in the table.
In the editions prior to MySQL 8.0.16, CREATE TABLE allows only the limited version of table CHECK constraint syntax, which is parsed and ignored:
CHECK (expr)
In MySQL 8.0.16, CREATE TABLE permits the core features of table and column CHECK constraints, for all storage engines. CREATE TABLE permits the following CHECK constraint syntax, for both table constraints and column constraints:
[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]
Where:
symbol is an optional parameter and specifies a name for the constraint. In case omitted, MySQL will generate a name from the table name, a literal _chk_, and an ordinal number (1, 2, 3, …).
Note, that constraint names can have a maximum length of 64 characters and they are case sensitive, but not accent sensitive.
expr specifies the constraint condition as a Boolean expression that must evaluate to TRUE or UNKNOWN (for NULL values) for each row of the table. If the condition evaluates to FALSE, it fails, and a constraint violation occurs.
ENFORCED is an optional clause and indicates whether the constraint is enforced. In case omitted or specified as ENFORCED, the constraint is created and enforced. If specified as NOT ENFORCED, the constraint is created but not enforced.
A CHECK constraint is specified as either a table constraint or column constraint:
• A table constraint does not appear within a column definition and can refer to any table column or columns. Forward references are permitted to columns appearing later in the table definition.
• A column constraint appears within a column definition and can refer only to that column.
Creating a CHECK constraint
The SQL standard syntax to create check constraint is supported in the column definition and table definition of CREATE TABLE and ALTER TABLE statements.
mysql> CREATE TABLE t1 (c1 INTEGER CONSTRAINT c1_chk CHECK (c1 > 0),
-> c2 INTEGER,
-> CONSTRAINT c2_chk CHECK (c2 > 0),
-> CONSTRAINT c1_c2_chk CHECK (c1 + c2 < 9999));
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
CONSTRAINT `c1_c2_chk` CHECK (((`c1` + `c2`) < 9999)),
CONSTRAINT `c1_chk` CHECK ((`c1` > 0)),
CONSTRAINT `c2_chk` CHECK ((`c2` > 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
As mentioned earlier, MySQL generates a name for any CHECK constraint without a specified one. To see the names generated for the table definition given above, use SHOW CREATE TABLE statement.
mysql> CREATE TABLE t1 (c1 INTEGER CONSTRAINT c1_chk CHECK (c1 > 0),
-> c2 INTEGER CHECK (c2 > 0),
-> c3 INTEGER,
-> c4 INTEGER,
-> CONSTRAINT c3_chk CHECK (c3 > 0),
-> CHECK (c4 > 0),
-> CONSTRAINT chk_all CHECK (c1 + c2 + c3 + c4 < 9999),
-> CHECK (c1 + c3 < 5000));
Query OK, 0 rows affected (0.06 sec)
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
`c3` int(11) DEFAULT NULL,
`c4` int(11) DEFAULT NULL,
CONSTRAINT `c1_chk` CHECK ((`c1` > 0)),
CONSTRAINT `c3_chk` CHECK ((`c3` > 0)),
CONSTRAINT `chk_all` CHECK (((((`c1` + `c2`) + `c3`) + `c4`) < 9999)),
CONSTRAINT `t1_chk_1` CHECK ((`c2` > 0)),
CONSTRAINT `t1_chk_2` CHECK ((`c4` > 0)),
CONSTRAINT `t1_chk_3` CHECK (((`c1` + `c3`) < 5000))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)
As you can see, t1_chk_1, t1_chk_2, t1_chk_3 are the names generated for CHECK constraints.
The SQL standard implies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema (database).
Therefore, CHECK constraint names must be unique per schema; no two tables within the same schema can have a same CHECK constraint name.
Generating constraint names on the basis of table names helps ensure schema uniqueness since table names must also be unique within the schema.
Note, that all check constraints are enforced by default. If you want to create a CHECK constraint but do not want to enforce it, then you should use the NOT ENFORCED clause.
mysql> CREATE TABLE t1 (c1 INTEGER CHECK (c1 > 0),
-> c2 INTEGER CHECK (c2 > 0) NOT ENFORCED);
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` int(11) DEFAULT NULL,
CONSTRAINT `t1_chk_1` CHECK ((`c1` > 0)),
CONSTRAINT `t1_chk_2` CHECK ((`c2` > 0)) /*!80016 NOT ENFORCED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
CHECK Constraints Support in dbForge Studio for MySQL and Mini Tools
dbForge Studio for MySQL 8.2 empowered with CHECK Constraints support has been just rolled out. We keep expanding functionality to please even the most demanding customers.
CHECK Constraints Support in Visual Table Editor:
CHECK Constraints Support in Visual Database Diagram:
CHECK Constraints Support in Generate Script As:
CHECK Constraints Support in Database Explorer:
CHECK Constraints Support in Database Backup:
CHECK Constraints Support in Database Refactoring:
CHECK Constraints Support in Schema Compare:
CHECK Constraints Support in Data Compare:
CHECK Constraints Support in Code Completion for ALTER TABLE … ADD CONSTRAINT, CREATE TABLE, and ALTER TABLE:
Assuring data integrity is a prime task when working with databases. Therefore CHECK constraint support in dbForge products for MySQL helps avoid a number of problems developers faced before. On the whole, it results in lowering error rates, time and effort saving, and increasing the data quality.
Tell Us What You Think
We welcome you to try the new version of dbForge Studio for MySQL and share your thoughts about the release with us. We’re always looking for ways to improve. Share your experience with our team and help us keep you satisfied.
