Movatterモバイル変換

CREATE TABLE permits the core features of table and columnCHECK constraints, for all storage engines.CREATE TABLE permits the followingCHECK constraint syntax, for both table constraints and column constraints:

[CONSTRAINT [symbol]] CHECK (expr) [[NOT] ENFORCED]

The optionalsymbol specifies a name for the constraint. If omitted, MySQL generates a name from the table name, a literal_chk_, and an ordinal number (1, 2, 3, ...). Constraint names have a maximum length of 64 characters. They are case-sensitive, but not accent-sensitive.

expr specifies the constraint condition as a boolean expression that must evaluate toTRUE orUNKNOWN (forNULL values) for each row of the table. If the condition evaluates toFALSE, it fails and a constraint violation occurs. The effect of a violation depends on the statement being executed, as described later in this section.

The optional enforcement clause indicates whether the constraint is enforced:

ACHECK constraint is specified as either a table constraint or column constraint:

Consider this table definition:

CREATE TABLE t1(  CHECK (c1 <> c2),  c1 INT CHECK (c1 > 10),  c2 INT CONSTRAINT c2_positive CHECK (c2 > 0),  c3 INT CHECK (c3 < 100),  CONSTRAINT c1_nonzero CHECK (c1 <> 0),  CHECK (c1 > c3));

The definition includes table constraints and column constraints, in named and unnamed formats:

As mentioned, MySQL generates a name for anyCHECK constraint specified without one. To see the names generated for the preceding table definition, useSHOW CREATE TABLE:

mysql> SHOW CREATE TABLE t1\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `c1` int(11) DEFAULT NULL,  `c2` int(11) DEFAULT NULL,  `c3` int(11) DEFAULT NULL,  CONSTRAINT `c1_nonzero` CHECK ((`c1` <> 0)),  CONSTRAINT `c2_positive` CHECK ((`c2` > 0)),  CONSTRAINT `t1_chk_1` CHECK ((`c1` <> `c2`)),  CONSTRAINT `t1_chk_2` CHECK ((`c1` > 10)),  CONSTRAINT `t1_chk_3` CHECK ((`c3` < 100)),  CONSTRAINT `t1_chk_4` CHECK ((`c1` > `c3`))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

The SQL standard specifies 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). Consequently,CHECK constraint names must be unique per schema; no two tables in the same schema can share aCHECK constraint name. (Exception: ATEMPORARY table hides a non-TEMPORARY table of the same name, so it can have the sameCHECK constraint names as well.)

Beginning generated constraint names with the table name helps ensure schema uniqueness because table names also must be unique within the schema.

CHECK condition expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

Foreign key referential actions (ON UPDATE,ON DELETE) are prohibited on columns used inCHECK constraints. Likewise,CHECK constraints are prohibited on columns used in foreign key referential actions.

CHECK constraints are evaluated forINSERT,UPDATE,REPLACE,LOAD DATA, andLOAD XML statements and an error occurs if a constraint evaluates toFALSE. If an error occurs, handling of changes already applied differs for transactional and nontransactional storage engines, and also depends on whether strict SQL mode is in effect, as described inStrict SQL Mode.

CHECK constraints are evaluated forINSERT IGNORE,UPDATE IGNORE,LOAD DATA ... IGNORE, andLOAD XML ... IGNORE statements and a warning occurs if a constraint evaluates toFALSE. The insert or update for any offending row is skipped.

If the constraint expression evaluates to a data type that differs from the declared column type, implicit coercion to the declared type occurs according to the usual MySQL type-conversion rules. SeeSection 14.3, “Type Conversion in Expression Evaluation”. If type conversion fails or results in a loss of precision, an error occurs.

The Information SchemaCHECK_CONSTRAINTS table provides information aboutCHECK constraints defined on tables. SeeSection 28.3.5, “The INFORMATION_SCHEMA CHECK_CONSTRAINTS Table”.