1.9.6. How MySQL Deals with Constraints
MySQL allows you to work both with transactional tables that
allow rollback and with non-transactional tables that do not.
Because of this, constraint handling is a bit different in MySQL
than in other DBMSs. We must handle the case when you have
inserted or updated a lot of rows in a non-transactional table
for which changes cannot be rolled back when an error occurs.
The basic philosophy is that MySQL Server tries to produce an
error for anything that it can detect while parsing a statement
to be executed, and tries to recover from any errors that occur
while executing the statement. We do this in most cases, but not
yet for all.
The options MySQL has when an error occurs are to stop the
statement in the middle or to recover as well as possible from
the problem and continue. By default, the server follows the
latter course. This means, for example, that the server may
coerce illegal values to the closest legal values.
Several SQL mode options are available to provide greater
control over handling of bad data values and whether to continue
statement execution or abort when errors occur. Using these
options, you can configure MySQL Server to act in a more
traditional fashion that is like other DBMSs that reject
improper input. The SQL mode can be set globally at server
startup to affect all clients. Individual clients can set the
SQL mode at runtime, which enables each client to select the
behavior most appropriate for its requirements. See
Section 5.2.5, “The Server SQL Mode”.
The following sections describe how MySQL Server handles
different types of constraints.
