(mysql.info.gz) constraint invalid data
Info Catalog
(mysql.info.gz) constraint primary key
(mysql.info.gz) Constraints
(mysql.info.gz) constraint enum
1.5.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 select more
traditional treatment of bad values such that the server rejects them
and aborts the statement in which they occur. This section describes
the default (forgiving) behavior of MySQL, as well as the newer strict
SQL mode and how it differs.
The following holds true when you are not using strict mode. If 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:
* If you try to store an out of range value in a numeric column,
MySQL Server instead stores zero, the smallest possible value, or
the largest possible value in the column.
* For strings, MySQL stores either the empty string or the longest
possible string that can be 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.
* If you try to store `NULL' into a column that doesn't take `NULL'
values, MySQL Server stores 0 or `''' (the empty string) instead.
* 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 date value `'0000-00-00'' is stored in the column
instead.
* 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. In general, this is the empty string for string
columns, 0 for numeric columns, and `'0000-00-00'' for date
columns. Implicit default values are discussed in `CREATE
TABLE' CREATE TABLE.
The reason for the preceding rules 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. Server SQL mode.
`STRICT_TRANS_TABLES' works like this: For transactional storage
engines, bad data values occurring anywhere in the statement causes the
to abort and roll back. For non-transactional storage engines, the
statement aborts if the error occurs in the first row to be inserted or
updated. (In this case, the statement can be regarded to leave the
table unchanged, just as for a transactional table.) Errors in rows
after the first do not abort the statement. 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, if
it can, all updates done so far.
For 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'.
Info Catalog
(mysql.info.gz) constraint primary key
(mysql.info.gz) Constraints
(mysql.info.gz) constraint enum
automatically generated byinfo2html