One can disable all constraint expression checks by setting the
check_constraint_checks variable to OFF. This is useful for example when
loading a table that violates some constraints that you want to later find and
fix in SQL.

Replication
-----------

In row-based replication, only the master checks constraints, and failed
statements will not be replicated. In statement-based replication, the slaves
will also check constraints. Constraints should therefore be identical, as
well as deterministic, in a replication environment.

Auto_increment
--------------

auto_increment columns are not permitted in check constraints. Before MariaDB
10.2.6, they were permitted, but would not work correctly. See MDEV-11117.

Examples
--------

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
           price DECIMAL,
           PRIMARY KEY(category, id)) ENGINE=INNODB;
CREATE TABLE customer (id INT NOT NULL,
           PRIMARY KEY (id)) ENGINE=INNODB;
CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
              product_category INT NOT NULL,
              product_id INT NOT NULL,
              customer_id INT NOT NULL,
              PRIMARY KEY(no),
              INDEX (product_category, product_id),
              FOREIGN KEY (product_category, product_id)
               REFERENCES product(category, id)
               ON UPDATE CASCADE ON DELETE RESTRICT,
              INDEX (customer_id),
              FOREIGN KEY (customer_id)
               REFERENCES customer(id)) ENGINE=INNODB;

The following examples will work from MariaDB 10.2.1 onwards.

Numeric constraints and comparisons:

CREATE TABLE t1 (a INT CHECK (a>2), b INT CHECK (b>2), CONSTRAINT a_greater
CHECK (a>b));

INSERT INTO t1(a) VALUES (1);
ERROR 4022 (23000): CONSTRAINT `a` failed for `test`.`t1`

INSERT INTO t1(a,b) VALUES (3,4);
ERROR 4022 (23000): CONSTRAINT `a_greater` failed for `test`.`t1`

INSERT INTO t1(a,b) VALUES (4,3);
Query OK, 1 row affected (0.04 sec)

Dropping a constraint:

ALTER TABLE t1 DROP CONSTRAINT a_greater;

Adding a constraint:

ALTER TABLE t1 ADD CONSTRAINT a_greater CHECK (a>b);

Date comparisons and character length:

CREATE TABLE t2 (name VARCHAR(30) CHECK (CHAR_LENGTH(name)>2), start_date
DATE, 
 end_date DATE CHECK (start_date IS NULL OR end_date IS NULL OR
start_date<end_date));

INSERT INTO t2(name, start_date, end_date) VALUES('Ione', '2003-12-15',
'2014-11-09');
Query OK, 1 row affected (0.04 sec)

INSERT INTO t2(name, start_date, end_date) VALUES('Io', '2003-12-15',
'2014-11-09');
ERROR 4022 (23000): CONSTRAINT `name` failed for `test`.`t2`

INSERT INTO t2(name, start_date, end_date) VALUES('Ione', NULL, '2014-11-09');
Query OK, 1 row affected (0.04 sec)

INSERT INTO t2(name, start_date, end_date) VALUES('Ione', '2015-12-15',
'2014-11-09');
ERROR 4022 (23000): CONSTRAINT `end_date` failed for `test`.`t2`

A misplaced parenthesis:

CREATE TABLE t3 (name VARCHAR(30) CHECK (CHAR_LENGTH(name>2)), start_date
DATE, 
 end_date DATE CHECK (start_date IS NULL OR end_date IS NULL OR
start_date<end_date));
Query OK, 0 rows affected (0.32 sec)

INSERT INTO t3(name, start_date, end_date) VALUES('Io', '2003-12-15',
'2014-11-09');
Query OK, 1 row affected, 1 warning (0.04 sec)

SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Io' |
+---------+------+----------------------------------------+

Compare the definition of table t2 to table t3. CHAR_LENGTH(name)>2 is very
different to CHAR_LENGTH(name>2) as the latter mistakenly performs a numeric
comparison on the name field, leading to unexpected results.

URL: https://mariadb.com/kb/en/constraint/https://mariadb.com/kb/en/constraint/