(mysql.info) constraint-invalid-data

(mysql.info) constraint-primary-key
 
 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