MariaDB supports the implementation of constraints at the
table-level using either CREATE TABLE or ALTER TABLE
statements. A table constraint restricts the data you can
add to the table. If you attempt to insert invalid data on a
column, MariaDB throws an error. 
 
Syntax
------ 
[CONSTRAINT [symbol]] constraint_expression
 
constraint_expression:
 | PRIMARY KEY [index_type] (index_col_name, ...)
[index_option] ...
 | FOREIGN KEY [index_name] (index_col_name, ...) 
 REFERENCES tbl_name (index_col_name, ...)
 [ON DELETE reference_option]
 [ON UPDATE reference_option]
 | UNIQUE [INDEX|KEY] [index_name]
 [index_type] (index_col_name, ...) [index_option] ...
 | CHECK (check_constraints)
 
index_type:
 USING {BTREE | HASH | RTREE}
 
index_col_name:
 col_name [(length)] [ASC | DESC]
 
index_option:
 | KEY_BLOCK_SIZE [=] value
 | index_type
 | WITH PARSER parser_name
 | COMMENT 'string'
 | CLUSTERING={YES|NO}
 
reference_option:
 RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
 
Description
----------- 
Constraints provide restrictions on the data you can add to
a table. This allows you to enforce data integrity from
MariaDB, rather than through application logic. When a
statement violates a constraint, MariaDB throws an error.
 
There are four types of table constraints:
 
Constraint | Description | 
 
PRIMARY KEY | Sets the column for referencing rows. Values
must be unique and not null. | 
 
FOREIGN KEY | Sets the column to reference the primary key
on another table. | 
 
UNIQUE | Requires values in column or columns only occur
once in the table. | 
 
CHECK | Checks whether the data meets the given condition. |

 
FOREIGN KEY Constraints
 
InnoDB supports foreign key constraints. The syntax for a
foreign key
constraint definition in InnoDB looks like this:
 
[CONSTRAINT [symbol]] FOREIGN KEY
 [index_name] (index_col_name, ...)
 REFERENCES tbl_name (index_col_name,...)
 [ON DELETE reference_option]
 [ON UPDATE reference_option]
 
reference_option:
 RESTRICT | CASCADE | SET NULL | NO ACTION
 
CHECK Constraints
 
From MariaDB 10.2.1, constraints are enforced. Before
MariaDB 10.2.1 constraint expressions were accepted in the
syntax but ignored.
 
In MariaDB 10.2.1 you can define constraints in 2 different
ways:
CHECK(expression) given as part of a column definition.
CONSTRAINT [constraint_name] CHECK (expression)
 
Before a row is inserted or updated, all constraints are
evaluated in the order they are defined. If any constraint
expression returns false, then the row will not be inserted
or updated.
One can use most deterministic functions in a constraint,
including UDFs.
 
CREATE TABLE t1 (a INT CHECK (a>2), b INT CHECK (b>2),
CONSTRAINT a_greater CHECK (a>b));
 
If you use the second format and you don't give a name to
the constraint, then the constraint will get an
automatically generated name. This is done so that you can
later delete the constraint with ALTER TABLE DROP
constraint_name.
 
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
 
From MariaDB 10.2.6, auto_increment columns are no longer
permitted in check constraints. Previously 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_date2)), start_date DATE, 
 end_date DATE CHECK (start_date IS NULL OR end_date IS NULL
OR start_date2 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/