How to fix ERROR 1452: Cannot add or update a child row: a foreign key constraint fails in MySQL

First, confirm that the value you're trying to reference actually exists in the parent table.

-- Example: Check if author_id 5 exists in authors table
SELECT * FROM authors WHERE id = 5;

If the query returns no rows, you need to create the parent record first. Always insert parent records before referencing them from child tables.

-- Insert the missing parent record
INSERT INTO authors (id, name) VALUES (5, 'John Doe');

-- Now you can insert the child record
INSERT INTO books (title, author_id) VALUES ('My Book', 5);

Verify that the foreign key column and the referenced primary key have identical data types and sizes.

-- Check the child table structure
DESCRIBE books;

-- Check the parent table structure
DESCRIBE authors;

Look for differences like:
- Child FK is INT but parent PK is BIGINT
- Child FK is VARCHAR(50) but parent PK is VARCHAR(100)
- Different numeric precision (DECIMAL(10,2) vs DECIMAL(10,3))

If you find a mismatch, alter the child table to match:

-- Make child FK match parent PK type
ALTER TABLE books MODIFY COLUMN author_id BIGINT;

If both columns are VARCHAR or CHAR type, verify they use the same character set and collation.

-- View table collation
SHOW CREATE TABLE authors;
SHOW CREATE TABLE books;

-- View column-specific collation
SELECT COLUMN_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN ('authors', 'books')
AND COLUMN_NAME IN ('id', 'author_id');

If collations differ, alter the child column to match:

-- Change collation to match parent table
ALTER TABLE books
MODIFY COLUMN author_id VARCHAR(255)
COLLATE utf8mb4_unicode_ci;

If you're migrating data or doing a bulk import where referential integrity is temporarily violated, you can disable FK checks during the operation. Use this with caution—re-enable checks immediately after.

-- Disable foreign key checks
SET FOREIGN_KEY_CHECKS = 0;

-- Perform your INSERT/UPDATE/DELETE operations
INSERT INTO books (title, author_id) VALUES ('Book', 999);

-- Re-enable foreign key checks
SET FOREIGN_KEY_CHECKS = 1;

WARNING: After re-enabling FOREIGN_KEY_CHECKS = 1, MySQL will NOT validate existing data. Make sure your data is consistent before enabling checks again.

Better approach for migrations: Ensure parent records exist before child records, maintaining referential integrity throughout.

View the exact foreign key constraint to understand which columns are involved.

-- See all constraints for a table
SHOW CREATE TABLE books;

-- Or query the information schema
SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'books'
AND COLUMN_NAME = 'author_id';

This shows you exactly which columns are linked and helps you debug the reference issue.