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 does not have a DEFAULT
value (and is not AUTO_INCREMENT) must be explicitly referenced in INSERT
statements. If not, an error like this is produced:
ERROR 1364 (HY000): Field 'col' doesn't have a default value
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 NULL Values in MariaDB:Inserting 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 DEFAULT()
function.
If the DEFAULT
keyword is used but the column does not have a DEFAULT
value, an error like this is produced:
ERROR 1364 (HY000): Field 'col' doesn't have a default value
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:
ERROR 1062 (23000): Duplicate entry 'dup_value' for key 'col'
To handle duplicates you can use the IGNORE
clause, INSERT ON DUPLICATE KEY UPDATE
or the REPLACE
statement.