How MySQL Finally Supports CHECK Constraints and Why It Matters for Data Integrity

Impact of Missing CHECK Constraints

MySQL historically supported only entity, domain, referential and unique constraints. Without a CHECK constraint, rows with invalid data (e.g., a negative age) can be stored, degrading data quality.

CREATE TABLE person (
  name VARCHAR(16),
  age INT
);

INSERT INTO person (name, age) VALUES ('张三岁', -32);
SELECT * FROM person;

The query returns a row with -32 as the age, illustrating the problem.

One workaround is to use an unsigned integer type, which prevents negative values but cannot enforce a minimum age such as > 18.

DROP TABLE IF EXISTS person;
CREATE TABLE person (
  name VARCHAR(16),
  age INT UNSIGNED
);

INSERT INTO person (name, age) VALUES ('张三岁', -32);
-- error 1264 (22003): Out of range value for column 'age'

Enforcing Age > 18 with CHECK (MySQL 8.0.16+)

Starting with MySQL 8.0.16 the engine evaluates CHECK constraints, allowing you to declare that age > 18 directly in the table definition.

SELECT @@version;  -- confirm you are on 8.0.16 or later

CREATE TABLE person (
  name VARCHAR(16),
  age INT,
  CONSTRAINT ck_person_001 CHECK (age > 18)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO person (name, age) VALUES ('张三岁', 17);
-- error 3819 (HY000): Check constraint 'ck_person_001' is violated

INSERT INTO person (name, age) VALUES ('张三岁', 19);
-- succeeds

Behavior in MySQL 8.0.15 and Earlier

In MySQL 8.0.15 and earlier the same CHECK clause is silently ignored. The constraint does not appear in SHOW CREATE TABLE, and invalid data can be inserted without error.

CREATE TABLE person (
  name VARCHAR(16),
  age INT,
  CONSTRAINT ck_person_001 CHECK (age > 18)
);

SHOW CREATE TABLE person;  -- the CHECK clause is omitted

INSERT INTO person (name, age) VALUES ('张三岁', 17);
-- row inserted successfully

Practical Steps

Verify the MySQL version with SELECT @@version; (must be 8.0.16 or later to enforce CHECK).

Define CHECK constraints in the CREATE TABLE statement when supported.

Confirm the constraint is stored using SHOW CREATE TABLE person;.

Test boundary values (e.g., age 17 and 19) to ensure the constraint behaves as expected.

For full syntax and additional considerations, refer to the official MySQL documentation: https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html