(mysql.info) constraint-enum

Info Catalog

(mysql.info) constraint-invalid-data

(mysql.info) constraints

 
 1.9.6.3 `ENUM' and `SET' Constraints
 ....................................
 
 `ENUM' and `SET' columns provide an efficient way to define columns
 that can contain only a given set of values. See  enum, and
  set. However, before MySQL 5.0.2, `ENUM' and `SET' columns do
 not provide true constraints on entry of invalid data:
 
    * `ENUM' columns always have a default value. If you specify no
      default value, then it is `NULL' for columns that can have `NULL',
      otherwise it is the first enumeration value in the column
      definition.
 
    * If you insert an incorrect value into an `ENUM' column or if you
      force a value into an `ENUM' column with `IGNORE', it is set to
      the reserved enumeration value of `0', which is displayed as an
      empty string in string context.
 
    * If you insert an incorrect value into a `SET' column, the
      incorrect value is ignored. For example, if the column can contain
      the values `'a'', `'b'', and `'c'', an attempt to assign
      `'a,x,b,y'' results in a value of `'a,b''.
 
 As of MySQL 5.0.2, you can configure the server to use strict SQL mode.
 See  server-sql-mode. With strict mode enabled, the definition
 of a `ENUM' or `SET' column does act as a constraint on values entered
 into the column. An error occurs for values that do not satisfy these
 conditions:
 
    * An `ENUM' value must be one of those listed in the column
      definition, or the internal numeric equivalent thereof. The value
      cannot be the error value (that is, 0 or the empty string). For a
      column defined as `ENUM('a','b','c')', values such as `''', `'d'',
      or `'ax'' are illegal and are rejected.
 
    * A `SET' value must be the empty string or a value consisting only
      of the values listed in the column definition separated by commas.
      For a column defined as `SET('a','b','c')', values such as `'d''
      or `'a,b,c,d'' are illegal and are rejected.
 
 Errors for invalid values can be suppressed in strict mode if you use
 `INSERT IGNORE' or `UPDATE IGNORE'. In this case, a warning is
 generated rather than an error. For `ENUM', the value is inserted as
 the error member (`0'). For `SET', the value is inserted as given except
 that any invalid substrings are deleted. For example, `'a,x,b,y''
 results in a value of `'a,b''.
 

Info Catalog

(mysql.info) constraint-invalid-data

(mysql.info) constraints