CertGrid CertGrid
Troubleshooting·MySQL

MySQL Data Types and Constraints

Three inserts that fail, each for a different reason and with a different error number. A constraint you never see fire is a constraint you do not know you have - so this guide fires all of them.

Schemas and Data Guide 7 of 45 Beginner

Written against the versions above. Strict SQL mode is the default from MySQL 5.7 onward. On an older server or one with `sql_mode` relaxed, the first of these truncates silently instead of failing, which is the behaviour strict mode was introduced to end.

Every command on this page ran on db-a01.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB

Before you start

  1. Start from data that is already there

    Three customers, inserted in one statement. Multi-row INSERT is one round trip and one transaction rather than three, which matters more than it looks at scale.

    Read the result table. id was filled by AUTO_INCREMENT, created_at by the server clock, and Grace's credit shows 0.00 - the column default, not the zero that was passed. Both mechanisms produced a value nobody typed per row.

    bash Example session 
    sudo mysql appdb -e "INSERT INTO customers (name, email, credit) VALUES ('Ada Lovelace','ada@example.com',120.50),('Grace Hopper','grace@example.com',0),('Katsuko Saruhashi','katsuko@example.com',75.25)"sudo mysql --table appdb -e "SELECT * FROM customers"+----+-------------------+---------------------+--------+---------------------+| id | name              | email               | credit | created_at          |+----+-------------------+---------------------+--------+---------------------+|  1 | Ada Lovelace      | ada@example.com     | 120.50 | 2026-08-27 12:47:19 ||  2 | Grace Hopper      | grace@example.com   |   0.00 | 2026-08-27 12:47:19 ||  3 | Katsuko Saruhashi | katsuko@example.com |  75.25 | 2026-08-27 12:47:19 |+----+-------------------+---------------------+--------+---------------------+

    Expected resultThree rows with generated ids and timestamps.

    Success conditionYou have rows to violate constraints against.

  2. Exceed the range of a column

    DECIMAL(10,2) means ten digits total, two after the point - so the largest value it holds is 99999999.99. The insert tries 99999999999.99.

    ERROR 1264 and exit 1. Nothing was written and nothing was truncated. This is strict SQL mode, and it is the default: without it MySQL would have stored the maximum value, returned a warning nobody reads, and left you with a wrong number that looks right.

    The error names the column and the row, which is what makes a multi-row insert debuggable.

    bash Example session 
    sudo mysql appdb -e "INSERT INTO customers (name, email, credit) VALUES ('Too Long', 'dup@example.com', 99999999999.99)" ; echo "exit=$?"ERROR 1264 (22003) at line 1: Out of range value for column 'credit' at row 1exit=1

    Expected resultERROR 1264 (22003): Out of range value for column 'credit' at row 1 and exit=1.

    Success conditionYou have seen strict mode refuse rather than silently truncate.

  3. Insert a duplicate into a unique column

    The email column was declared UNIQUE, and the previous guide showed MySQL turning that into a named index.

    ERROR 1062, and read what it names: the value AND the key, customers.email. On a table with several unique constraints that is the difference between a two-second fix and a guessing game.

    This is the error behind most failed application signups, and it is a feature - the database refusing to let two accounts share an identity.

    bash Example session 
    sudo mysql appdb -e "INSERT INTO customers (name, email) VALUES ('Ada Again','ada@example.com')" ; echo "exit=$?"ERROR 1062 (23000) at line 1: Duplicate entry 'ada@example.com' for key 'customers.email'exit=1

    Expected resultERROR 1062 (23000): Duplicate entry 'ada@example.com' for key 'customers.email' and exit=1.

    Success conditionYou can read which constraint rejected a row from the error alone.

  4. Omit something that has no default

    An insert that supplies email but not name.

    ERROR 1364, and the wording is precise: not "name is required" but *"Field 'name' doesn't have a default value"*. That is the real rule. A NOT NULL column with a default is fine to omit; one without has nothing to fall back on, so the statement fails.

    Compare with credit, which is also NOT NULL and was omitted happily in the first step - because it has DEFAULT 0.00.

    bash Example session 
    sudo mysql appdb -e "INSERT INTO customers (email) VALUES ('noname@example.com')" ; echo "exit=$?"ERROR 1364 (HY000) at line 1: Field 'name' doesn't have a default valueexit=1

    Expected resultERROR 1364 (HY000): Field 'name' doesn't have a default value and exit=1.

    Success conditionYou know the difference between NOT NULL and NOT NULL with a default.

  5. Store four-byte Unicode and count it two ways

    A name with an accent and an emoji. It inserts without complaint, which is the point - the column is utf8mb4 because the database was created that way.

    The two length functions are the lesson. CHAR_LENGTH is 15 and LENGTH is 19. Fifteen characters, nineteen bytes: the accented character costs two and the emoji four.

    This is why VARCHAR(80) means 80 characters rather than 80 bytes, and why byte-based length checks in application code reject names that are perfectly valid.

    bash Example session 
    sudo mysql appdb -e "INSERT INTO customers (name, email) VALUES ('Zoë Washburne 🚀','zoe@example.com')"sudo mysql --table appdb -e "SELECT id, name, CHAR_LENGTH(name) AS chars, LENGTH(name) AS bytes FROM customers WHERE id > 3"+----+---------------------+-------+-------+| id | name                | chars | bytes |+----+---------------------+-------+-------+|  5 | Zoë Washburne 🚀      |    15 |    19 |+----+---------------------+-------+-------+

    Expected resultThe insert succeeds, then one row with chars 15 and bytes 19.

    Success conditionYou can tell characters from bytes and know which one the column limits.

Troubleshooting

Official sources

← All MySQL guides