Most INSERT failures fall into a handful of kinds. Learn to recognise them.
CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(30) NOT NULL UNIQUE);
CREATE TABLE staff (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
email VARCHAR(50) NOT NULL UNIQUE,
department_id INT NOT NULL,
salary DECIMAL(8,2) NOT NULL,
hired_on DATE NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments (id)
);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Sales');
INSERT INTO staff (name, email, department_id, salary, hired_on) VALUES ('Asha', 'asha@example.com', 1, 90000, '2024-01-10');1. Duplicate entry (1062)
INSERT INTO staff (name, email, department_id, salary, hired_on) VALUES ('Asha 2', 'asha@example.com', 1, 50000, '2025-01-01');ERROR 1062 (23000): Duplicate entry 'asha@example.com' for key 'staff.email'A PRIMARY KEY or UNIQUE value already exists. The message names the value and the key. Fix: use a different value, or an upsert if updating is what you want (Upsert).
2. Column count does not match (1136)
INSERT INTO staff (name, email) VALUES ('Ben', 'ben@example.com', 2);ERROR 1136 (21S01): Column count doesn't match value count at row 1Three values for two columns. Fix: count them. This is common after a column list is edited and the values are not.
3. Field has no default value (1364)
INSERT INTO staff (name, email, department_id, hired_on) VALUES ('Chitra', 'chitra@example.com', 1, '2025-02-01');ERROR 1364 (HY000): Field 'salary' doesn't have a default valueThe salary column is NOT NULL with no default, and you did not give it. Fix: supply a value, or give the column a sensible default in the table.
4. Data too long (1406)
INSERT INTO staff (name, email, department_id, salary, hired_on) VALUES ('A name that is much too long', 'long@example.com', 1, 1000, '2025-02-01');ERROR 1406 (22001): Data too long for column 'name' at row 1The text is longer than the column. Fix: shorten it, or widen the column if the limit is a real mistake. Do not turn off strict mode.
5. Foreign key failure (1452)
INSERT INTO staff (name, email, department_id, salary, hired_on) VALUES ('Dev', 'dev@example.com', 99, 1000, '2025-02-01');ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn`.`staff`, CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`))Department 99 does not exist. Fix: insert the parent row first, or use the right id.
6. Incorrect value (1366 or 1292)
INSERT INTO staff (name, email, department_id, salary, hired_on) VALUES ('Esha', 'esha@example.com', 1, 'lots', '2025-02-01');ERROR 1366 (HY000): Incorrect decimal value: 'lots' for column 'salary' at row 1INSERT INTO staff (name, email, department_id, salary, hired_on) VALUES ('Farid', 'farid@example.com', 1, 1000, '31-12-2025');ERROR 1292 (22007): Incorrect date value: '31-12-2025' for column 'hired_on' at row 1The type does not match, or the date is in the wrong format. Fix: use YYYY-MM-DD for dates and plain numbers for numeric columns.
7. Column does not exist (1054)
INSERT INTO staff (name, mail, department_id, salary, hired_on) VALUES ('Gita', 'gita@example.com', 1, 1000, '2025-02-01');ERROR 1054 (42S22): Unknown column 'mail' in 'field list'A typo in the column name. Fix: DESCRIBE staff;
8. Other errors you may meet
| Error | Meaning |
|---|---|
| 1045 / 1142 | Access denied. Your account lacks the INSERT privilege on the table |
| 1205 | Lock wait timeout. Another transaction holds the row |
| 1213 | Deadlock. Retry the transaction |
| 1153 | Packet too large. Lower the batch size or raise max_allowed_packet |
| 1118 / 1074 | Row or column definition too large |
How to find the real problem fast
- Read the error number and the message. They name the column and the value.
- Reduce a big multi-row
INSERTto one row and run it alone. - Check
DESCRIBEandSHOW CREATE TABLEfor the real column rules. - In application code, log the full database error (server side), but show users a plain message.