INSERT - Default & Duplicate Values

Default Values

If the SQL_MODE contains STRICT_TRANS_TABLES and you are inserting into a transactional table (like InnoDB), or if the SQL_MODE contains STRICT_ALL_TABLES, all NOT NULL columns which do not have a DEFAULT value (and are not AUTO_INCREMENT) must be explicitly referenced in INSERT statements. If not, an error like this is produced:

In all other cases, if a NOT NULL column without a DEFAULT value is not referenced, an empty value will be inserted (for example, 0 for INTEGER columns and '' for CHAR columns). See for examples.

If a NOT NULL column having a DEFAULT value is not referenced, NULL will be inserted.

If a NULL column having a DEFAULT value is not referenced, its default value will be inserted. It is also possible to explicitly assign the default value using the DEFAULT keyword or the function.

If the DEFAULT keyword is used but the column does not have a DEFAULT value, an error like this is produced:

Duplicate Values

By default, if you try to insert a duplicate row and there is a UNIQUE index, INSERT stops and an error like this is produced:

To handle duplicates you can use the clause, or the statement. Note that the IGNORE and DELAYED options are ignored when you use .

See Also

_{This page is licensed: CC BY-SA / Gnu FDL}