# Strict Mode

__*Strict SQL mode*__
__*STRICT_TRANS_TABLES*__
__*STRICT_ALL_TABLES*__

+ Mode values: STRICT_TRANS_TABLES or STRICT_ALL_TABLES
+ When strict mode is not enabled, TiDB converts invalid values according to table schema and constraints
  + Transformations occur when you add, replace, or update existing data:
  + TiDB adjusts invalid values to the nearest valid values
    + Generates a warning, which you can view them by: `SHOW WARNINGS`  
+ In strict mode, TiDB generates an error and aborts the statement

------------------------------------------------------------

TiDB is generally more forgiving when supplied with invalid data than many traditional database systems. For example, if you write a negative value into an UNSIGNED column, TiDB converts it to zero, which is the nearest legal value for the column's data type. In strict mode, the server rejects values that are out of range, have an incorrect data type, or are missing for columns that have no default value.

When you generate an error while using a transactional table (such as those created with the default storage engine InnoDB) and STRICT_TRANS_TABLES is enabled, the statement aborts.

For a non-transactional table, TiDB aborts the statement if the error occurs in the first (or only) row of an insert operation. If an error occurs in the second or later insert, TiDB adjusts any invalid value to a legal value, inserts it, and generates only a warning. This is because the earlier inserts were successful.

STRICT_ALL_TABLES is similar to STRICT_TRANS_TABLES, but for non-transactional tables, it causes statements to abort even for errors in the second or later rows of a multiple-row insert. This might result in a partial update. To avoid this situation when using STRICT_ALL_TABLES, you must use single-row statements that can be aborted without changing the table.


- STRICT_TRANS_TABLES: Strict mode, which strictly validates the data, inserts it into the table when an error occurs, and return an error.
  - STRICT_TRANS_TABLES enables strict mode for transactional storage engines, and rolls back the entire statement after inserting illegal values (supported)
  - STRICT_ALL_TABLES For transactional tables, after writing illegal values, the entire transaction statement is rolled back (supported)