Syntax\n------\n\n[CONSTRAINT [symbol]] constraint_expression\n\nconstraint_expression:\n  | PRIMARY KEY [index_type] (index_col_name, ...) [index_option] ...\n  | FOREIGN KEY [index_name] (index_col_name, ...) \n       REFERENCES tbl_name (index_col_name, ...)\n       [ON DELETE reference_option]\n       [ON UPDATE reference_option]\n  | UNIQUE [INDEX|KEY] [index_name]\n       [index_type] (index_col_name, ...) [index_option] ...\n  | CHECK (check_constraints)\n\nindex_type:\n  USING {BTREE | HASH | RTREE}\n\nindex_col_name:\n  col_name [(length)] [ASC | DESC]\n\nindex_option:\n  | KEY_BLOCK_SIZE [=] value\n  | index_type\n  | WITH PARSER parser_name\n  | COMMENT 'string'\n  | CLUSTERING={YES|NO}\n\nreference_option:\n  RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT\n\nDescription\n-----------\n\nConstraints provide restrictions on the data you can add to a table. This allows to enforce data integrity in MariaDB, rather than through application logic. When a statement violates a constraint, MariaDB throws an error.\n\nThere are four types of table constraints:\n\n PRIMARY KEY – Sets the column for referencing rows. Values must be UNIQUE and NOT NULL.&#x20;\n   This constraint is documented here.\n FOREIGN KEY – Sets the column to reference the primary key on another table.&#x20;\n   This constraint is documented here and, in more detail, here and here.\n UNIQUE – Requires values in column or columns only occur once in the table.&#x20;\n   This constraint is documented here.\n CHECK – Checks whether the data meets the given condition.&#x20;\n   This constraint is documented here and, in more detail, here.\n\nThe Information Schema TABLE_CONSTRAINTS Table contains information about tables that have constraints:\n\n``sql\nSELECT CONSTRAINT_SCHEMA, CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPE  FROM information_schema.TABLE_CONSTRAINTS LIMIT 5;\n+-------------------+----------------------+---------------+-----------------+\n| CONSTRAINT_SCHEMA | CONSTRAINT_NAME      | TABLE_NAME    | CONSTRAINT_TYPE |\n+-------------------+----------------------+---------------+-----------------+\n| nation            | PRIMARY              | countries     | PRIMARY KEY     |\n| nation            | country_code2        | countries     | UNIQUE          |\n| nation            | country_code3        | countries     | UNIQUE          |\n| nation            | countries_ibfk_1     | countries     | FOREIGN KEY     |\n| nation            | PRIMARY              | regions       | PRIMARY KEY     |\n+-------------------+----------------------+---------------+-----------------+\n`\n\nFOREIGN KEY Constraints\n\nInnoDB supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB looks like this:\n\n`sql\n[CONSTRAINT [symbol]] FOREIGN KEY\n    [index_name] (index_col_name, ...)\n    REFERENCES tbl_name (index_col_name,...)\n    [ON DELETE reference_option]\n    [ON UPDATE reference_option]\n\nreference_option:\n    RESTRICT | CASCADE | SET NULL | NO ACTION\n`\n\nThe symbol clause is optional. If you omit it, MariaDB automatically sets one.&#x20;\n\nThe Information Schema REFERENTIAL_CONSTRAINTS table has more information about foreign keys:\n\n`sql\nSELECT  FROM information_schema.REFERENTIAL_CONSTRAINTS LIMIT 2 \\G\n*********************** 1. row *********************\n       CONSTRAINT_CATALOG: def\n        CONSTRAINT_SCHEMA: nation\n          CONSTRAINT_NAME: countries_ibfk_1\nUNIQUE_CONSTRAINT_CATALOG: def\n UNIQUE_CONSTRAINT_SCHEMA: nation\n   UNIQUE_CONSTRAINT_NAME: PRIMARY\n             MATCH_OPTION: NONE\n              UPDATE_RULE: RESTRICT\n              DELETE_RULE: RESTRICT\n               TABLE_NAME: countries\n    REFERENCED_TABLE_NAME: regions\n********************* 2. row ***********************\n       CONSTRAINT_CATALOG: def\n        CONSTRAINT_SCHEMA: nation\n          CONSTRAINT_NAME: regions_ibfk_1\nUNIQUE_CONSTRAINT_CATALOG: def\n UNIQUE_CONSTRAINT_SCHEMA: nation\n   UNIQUE_CONSTRAINT_NAME: PRIMARY\n             MATCH_OPTION: NONE\n              UPDATE_RULE: RESTRICT\n              DELETE_RULE: RESTRICT\n               TABLE_NAME: regions\n    REFERENCED_TABLE_NAME: continents\n`\n\nFrom MariaDB 12.1, multiple tables in the same database are allowed to use the same constraint name.\n\nConsider this example, which creates two tables, then adds constraints to them:\n\n`sql\nCREATE TABLE t1(a int, b int, key(a),key(b))engine=innodb;\nCREATE TABLE t2(a int, b int, key(a),key(b))engine=innodb;\nALTER TABLE t2 ADD CONSTRAINT c FOREIGN KEY (b) REFERENCES t1(a);\nALTER TABLE t1 ADD CONSTRAINT c FOREIGN KEY (b) REFERENCES t2(a);\n`\n\nPreviously, the last statement failed because of duplicate constraint names:\n\n`\nmysqltest: At line 5: query 'alter table t1 add constraint c foreign key (b) references t2(a)' failed: ER_CANT_CREATE_TABLE (1005): Can't create table test.t1 (errno: 121 "Duplicate key on write or update")\n`\n\nNow, the statement works, because internally, names are assigned that aren't duplicates. (User-visible names can be duplicates, but internally they're prefixed with the schema and table names.)\n\nCHECK Constraints\n\nConstraints are enforced. You can define constraints in 2 different ways:\n\n CHECK(expression) given as part of a column definition.\n* CONSTRAINT [constraint_name] CHECK (expression)\n\nBefore 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 is not inserted or updated. You can use most deterministic functions in a constraint, including UDFs.\n\n`sql\nCREATE TABLE t1 (a INT CHECK (a>2), b INT CHECK (b>2), CONSTRAINT a_greater CHECK (a>b));\n`\n\nIf you use the CONSTRAINT [constraint_name] CHECK (expression) format and don't give a name to the constraint, the constraint gets an automatically generated name. This is done so that you can later delete it with ALTER TABLE DROP _constraint_.\n\nYou 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.\n\nReplication\n\nIn row-based replication, only the master checks constraints, and failed statements are not replicated. In statement-based replication, the slaves also check constraints. Constraints should therefore be identical, as well as deterministic, in a replication environment.\n\nAuto_increment\n\nauto_increment columns are not permitted in check constraints.\n\nExamples\n--------\n\nCREATE TABLE product (category INT NOT NULL, id INT NOT NULL,\n                      price DECIMAL,\n                      PRIMARY KEY(category, id)) ENGINE=INNODB;\nCREATE TABLE customer (id INT NOT NULL,\n                       PRIMARY KEY (id)) ENGINE=INNODB;\nCREATE TABLE product_order (NO INT NOT NULL AUTO_INCREMENT,\n                            product_category INT NOT NULL,\n                            product_id INT NOT NULL,\n                            customer_id INT NOT NULL,\n                            PRIMARY KEY(NO),\n                            INDEX (product_category, product_id),\n                            FOREIGN KEY (product_category, product_id)\n                              REFERENCES product(category, id)\n                              ON UPDATE CASCADE ON DELETE RESTRICT,\n                            INDEX (customer_id),\n                            FOREIGN KEY (customer_id)\n                              REFERENCES customer(id)) ENGINE=INNODB;\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-definition/constraint