(mysql.info.gz) constraint enum
Info Catalog
(mysql.info.gz) constraint invalid data
(mysql.info.gz) Constraints
1.5.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. However, before MySQL
5.0.2, `ENUM' and `SET' are not real constraints. This is for the same
reasons that `NOT NULL' is not honored. constraint invalid
data.
`ENUM' columns always have a default value. If you don't specify a
default value, then it will be `NULL' for columns that can have `NULL',
otherwise the first enumeration value is used as the default value.
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. `ENUM' ENUM.
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''. `SET' SET.
As of MySQL 5.0.2, you can configure the server to use strict SQL mode.
Server SQL mode. When strict mode is not enabled, values
entered into `ENUM' and `SET' columns are handled as just described for
MySQL 4.x. However, if strict mode is 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'',
and `'ax'' are illegal and are rejected.
* A `SET' value must be the empty string or a value consisting of
one or more of the values listed in the column definition
separated by commas. For a column defined as `SET('a','b','c')',
values such as `'d'', and `'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'', as described earlier.
Info Catalog
(mysql.info.gz) constraint invalid data
(mysql.info.gz) Constraints
automatically generated byinfo2html