| |
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 Section 11.4.4, “The ENUM Type”, and
Section 11.4.5, “The SET Type”. 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 Section 5.2.5, “The 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'.
|
|
