(mysql.info) constraint-invalid-data
Info Catalog
(mysql.info) constraint-primary-key
(mysql.info) constraints
(mysql.info) constraint-enum
1.9.6.2 Constraints on Invalid Data
...................................
Before MySQL 5.0.2, MySQL is forgiving of illegal or improper data
values and coerces them to legal values for data entry. In MySQL 5.0.2
and up, that remains the default behavior, but you can change the
server SQL mode to select more traditional treatment of bad values such
that the server rejects them and aborts the statement in which they
occur. server-sql-mode.
This section describes the default (forgiving) behavior of MySQL, as
well as the newer strict SQL mode and how it differs.
If you are not using strict mode, then whenever you insert an
`incorrect' value into a column, such as a `NULL' into a `NOT NULL'
column or a too-large numeric value into a numeric column, MySQL sets
the column to the `best possible value' instead of producing an error:
The following rules describe in more detail how this works:
* If you try to store an out of range value into a numeric column,
MySQL Server instead stores zero, the smallest possible value, or
the largest possible value, whichever is closest to the invalid
value. column.
* For strings, MySQL stores either the empty string or as much of
the string as can be stored in the column.
* If you try to store a string that doesn't start with a number into
a numeric column, MySQL Server stores 0.
* Invalid values for `ENUM' and `SET' columns ae handled as
described in constraint-enum.
* MySQL allows you to store certain incorrect date values into
`DATE' and `DATETIME' columns (such as `'2000-02-31'' or
`'2000-02-00''). The idea is that it's not the job of the SQL
server to validate dates. If MySQL can store a date value and
retrieve exactly the same value, MySQL stores it as given. If the
date is totally wrong (outside the server's ability to store it),
the special `zero' date value `'0000-00-00'' is stored in the
column instead.
* If you try to store `NULL' into a column that doesn't take `NULL'
values, an error occurs for single-row `INSERT' statements. For
multiple-row `INSERT' statements or for `INSERT INTO ... SELECT'
statements, MySQL Server stores the implicit default value for the
column data type. In general, this is `0' for numeric types, the
empty string (`''') for string types, and the `zero' value for
date and time types. Implicit default values are discussed in
data-type-defaults.
* If an `INSERT' statement specifies no value for a column, MySQL
inserts its default value if the column definition includes an
explicit `DEFAULT' clause. If the definition has no such `DEFAULT'
clause, MySQL inserts the implicit default value for the column
data type.
The reason for using the preceding rules in non-strict mode is that we
can't check these conditions until the statement has begun executing.
We can't just roll back if we encounter a problem after updating a few
rows, because the storage engine may not support rollback. The option
of terminating the statement is not that good; in this case, the update
would be `half done,' which is probably the worst possible scenario. In
this case, it's better to `do the best you can' and then continue as if
nothing happened.
In MySQL 5.0.2 and up, you can select stricter treatment of input
values by using the `STRICT_TRANS_TABLES' or `STRICT_ALL_TABLES' SQL
modes:
SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';
`STRICT_TRANS_TABLES' enables strict mode for transactional storage
engines, and also to some extent for non-transactional engines. It
works like this:
* For transactional storage engines, bad data values occurring
anywhere in a statement cause the statement to abort and roll back.
* For non-transactional storage engines, a statement aborts if the
error occurs in the first row to be inserted or updated. (When the
error occurs in the first row, the statement can be aborted to
leave the table unchanged, just as for a transactional table.)
Errors in rows after the first do not abort the statement, because
the table has already been changed by the first row. Instead, bad
data values are adjusted and result in warnings rather than
errors. In other words, with `STRICT_TRANS_TABLES', a wrong value
causes MySQL to roll back all updates done so far, if that can be
done without changing the table. But once the table has been
changed, further errors result in adjustments and warnings.
For even stricter checking, enable `STRICT_ALL_TABLES'. This is the
same as `STRICT_TRANS_TABLES' except that for non-transactional storage
engines, errors abort the statement even for bad data in rows following
the first row. This means that if an error occurs partway through a
multiple-row insert or update for a non-transactional table, a partial
update results. Earlier rows are inserted or updated, but those from
the point of the error on are not. To avoid this for non-transactional
tables, either use single-row statements or else use
`STRICT_TRANS_TABLES' if conversion warnings rather than errors are
acceptable. To avoid problems in the first place, do not use MySQL to
check column content. It is safest (and often faster) to let the
application ensure that it passes only legal values to the database.
With either of the strict mode options, you can cause errors to be
treated as warnings by using `INSERT IGNORE' or `UPDATE IGNORE' rather
than `INSERT' or `UPDATE' without `IGNORE'.
Info Catalog
(mysql.info) constraint-primary-key
(mysql.info) constraints
(mysql.info) constraint-enum
automatically generated byinfo2html