How to fix ERROR 1005: Can't create table in MySQL

-- WRONG - INT UNSIGNED vs INT mismatch
CREATE TABLE parent_table (
  id INT UNSIGNED PRIMARY KEY
);

CREATE TABLE child_table (
  id INT PRIMARY KEY,
  parent_id INT,  -- WRONG: should be INT UNSIGNED
  FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

-- CORRECT - Types match exactly
CREATE TABLE parent_table (
  id INT UNSIGNED PRIMARY KEY
);

CREATE TABLE child_table (
  id INT PRIMARY KEY,
  parent_id INT UNSIGNED,  -- Matches parent_table.id type
  FOREIGN KEY (parent_id) REFERENCES parent_table(id)
);

-- Check current storage engine
SHOW CREATE TABLE your_table;

-- Create or alter tables to use InnoDB
CREATE TABLE parent_table (
  id INT UNSIGNED PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE child_table (
  id INT PRIMARY KEY,
  parent_id INT UNSIGNED,
  FOREIGN KEY (parent_id) REFERENCES parent_table(id)
) ENGINE=InnoDB;

-- If converting existing table
ALTER TABLE your_table ENGINE=InnoDB;

SHOW VARIABLES LIKE 'have_innodb';
-- Should return 'YES'

-- List all tables in current database
SHOW TABLES;

-- Verify the referenced column exists
SHOW COLUMNS FROM parent_table;

-- Check for correct spelling and case sensitivity
-- Table and column names may be case-sensitive depending on OS
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_database'
AND TABLE_NAME = 'parent_table'
AND COLUMN_NAME = 'id';

-- View all constraints for a table
SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'child_table';

-- If adding a constraint, use a unique name
ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent_unique_name FOREIGN KEY (parent_id)
REFERENCES parent_table(id);

-- Drop an existing constraint if needed
ALTER TABLE child_table DROP FOREIGN KEY old_constraint_name;

-- View current character set and collation
SHOW CREATE TABLE your_table;

-- Create tables with matching collation
CREATE TABLE parent_table (
  code VARCHAR(50) COLLATE utf8mb4_unicode_ci PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE child_table (
  id INT PRIMARY KEY,
  code_ref VARCHAR(50) COLLATE utf8mb4_unicode_ci,
  FOREIGN KEY (code_ref) REFERENCES parent_table(code)
) ENGINE=InnoDB;

-- Or convert existing table
ALTER TABLE child_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Run this immediately after the failed CREATE/ALTER TABLE command
SHOW ENGINE INNODB STATUS;

-- Look for the "LATEST FOREIGN KEY ERROR" section
-- It will show:
-- - Which constraint failed
-- - Why it failed (type mismatch, missing index, etc.)
-- - Which tables and columns are involved

-- Example output might show:
-- 150318 16:58:05 Error in foreign key constraint of table db/child_table:
-- Could not find a matching row in the referenced table parent_table

-- Disable foreign key checks (careful - can cause inconsistent data)
SET FOREIGN_KEY_CHECKS = 0;

-- Now create your tables or import your dump
CREATE TABLE child_table (
  id INT PRIMARY KEY,
  parent_id INT UNSIGNED,
  FOREIGN KEY (parent_id) REFERENCES parent_table(id)
) ENGINE=InnoDB;

-- Re-enable checks
SET FOREIGN_KEY_CHECKS = 1;

-- Verify data integrity
-- Make sure all parent_id values actually exist in parent_table
SELECT COUNT(*) FROM child_table
WHERE parent_id NOT IN (SELECT id FROM parent_table);