Home/Error Library/mysql-1215-cannot-add-foreign-key-constraint

MySQL

MySQL 5.7–8.4 LTSData Integrity & Schema

8 min read

ERROR 1215 (HY000): Cannot add foreign key constraint

MySQL Error 1215 — Why Is the Foreign Key Failing to Create?

Diagnosing unhelpful Error 1215 via SHOW ENGINE INNODB STATUS LATEST FOREIGN KEY ERROR, resolving signed/unsigned mismatches, collations, and leftmost index rules.

Reviewed by:Principal Database Reliability Architect

Tested Versions: MySQL 8.4 LTS, 8.0.40 & 5.7.44 (InnoDB / Docker 29.7)

Operational Verification & Reliability Metadata

Last Technical Review: 30 Ekim 2026

Tested VersionsMySQL 8.4 LTS, 8.0.40 & 5.7.44 (InnoDB, Docker 29.7)

Required PrivilegesALTER TABLE on target tables; PROCESS privilege to inspect SHOW ENGINE INNODB STATUS

Risk Assessment

HighBypassing validation via FOREIGN_KEY_CHECKS = 0 permits orphaned child records that explode during subsequent DML

Expected OutputSuccessful constraint addition after resolving type, signedness, collation, and index mismatches revealed in LATEST FOREIGN KEY ERROR

Rollback Procedure:Align COLUMN_TYPE, CHARACTER_SET, and COLLATION identically and verify leftmost parent index

MySQL 8.4 Docs: Foreign Key Requirements & InnoDB Status

First 60 Seconds Emergency Protocol:

The error string is a symptom, not the root cause. Do not restart the server blindly. Run the diagnostic queries below to pinpoint the actual failure mode.

▸Symptom

plaintext

ERROR 1215 (HY000): Cannot add foreign key constraint

MySQL Error 1215 is notorious for being completely uninformative: It declines to indicate which table, column, or constraint violated relational rules.

However, InnoDB records the precise failure reason in its engine status log:

sql

SHOW ENGINE INNODB STATUSG

Scroll to the `LATEST FOREIGN KEY ERROR` section:

plaintext

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2026-10-30 00:45:12 0x7f8 Error in foreign key constraint creation
for table `app`.`order_items`:
Foreign key constraint 'fk_items_orders' cannot be created:
  referencing column `order_id` (bigint unsigned) is not compatible
  with referenced column `id` (int)

The exact incompatibility is explicitly detailed. Inspecting this section is mandatory when troubleshooting Error 1215.

NOTE

On MySQL 8.0+, this error frequently manifests as the more verbose `ERROR 3780 (HY000)`.

▸Diagnostic Query: Side-by-Side Column Inspection

sql

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE,
       CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'app'
  AND ((TABLE_NAME = 'orders' AND COLUMN_NAME = 'id')
    OR (TABLE_NAME = 'order_items' AND COLUMN_NAME = 'order_id'));

▸The Seven Root Causes

1Signed vs Unsigned Integer Mismatch:

The single most common culprit. A parent BIGINT UNSIGNED column cannot be referenced by a child BIGINT (signed) column. Both consume 8 bytes, but their domains differ.

1Character Set or Collation Discrepancy:

String columns must match identical charsets and collations (utf8mb4_0900_ai_ci vs utf8mb4_general_ci will fail).

1Missing Leftmost Index on Referenced Column:

The referenced parent column must be the leftmost prefix of a key index.

1Existing Orphaned Records in Child Table:

When adding constraints via ALTER TABLE, if child rows exist with values absent from the parent table, creation fails.

1`ON DELETE SET NULL` on `NOT NULL` Column:

Contradictory semantic declaration.

1Non-InnoDB Storage Engine:

MyISAM tables do not support foreign keys. Both relations must run on InnoDB.

1Duplicate Constraint Name Across Schema:

Foreign key constraint identifiers must be unique schema-wide.

▸Step-by-Step Resolution

1. Reconcile Data Types and Character Sets

sql

ALTER TABLE app.order_items
  MODIFY order_id BIGINT UNSIGNED NOT NULL;

2. Apply Foreign Key with Explicit Referential Actions

sql

ALTER TABLE app.order_items
  ADD CONSTRAINT fk_items_orders FOREIGN KEY (order_id)
      REFERENCES app.orders (id) ON DELETE RESTRICT;

CAUTION

Avoid bypassing validation using SET FOREIGN_KEY_CHECKS = 0;. This masks existing orphaned child records and results in future runtime failures during routine DML operations.

▸Related Error Guides & Hub Links

•MySQL Error 1452 Foreign Key Constraint (H-14) — Parent row absence during insertion
•MySQL Error 1451 Cannot Delete Parent Row (H-38) — Referential blocks during deletion
•MySQL Error 1071 Specified Key Too Long (H-22) — InnoDB index key prefixes
•MySQL Hub — Relational schema architecture and InnoDB engine internals

Other Database Error Triage Playbooks

Other battle-tested diagnostic guides for high-severity database errors.

All Errors (50)

MYSQL4 min