📋

Description

MariaDB Error 1215 occurs when the database system is unable to successfully create or add a foreign key constraint to a table. This error typically arises when there's a mismatch or violation of the rules required to establish referential integrity between two tables.

💬

Error Message

Cannot add foreign key constraint

🔍

Known Causes

4 known causes

⚠️

Data Type Mismatch

The data type, length, or collation of the foreign key column(s) in the child table does not exactly match the referenced primary or unique key column(s) in the parent table.

⚠️

Referenced Column Not Indexed

The column(s) in the parent table that the foreign key references are not indexed (i.e., they are not a primary key or a unique index).

⚠️

Parent Table or Column Missing

The referenced parent table or the specific column(s) within the parent table do not exist in the database or are incorrectly specified.

⚠️

Existing Data Violation

The child table already contains data with values in the foreign key column(s) that do not have corresponding matching values in the parent table's referenced column(s).

🛠️

Solutions

4 solutions available

1. Ensure Referenced Table Exists easy

Parent table must exist before creating FK

1

Check if parent table exists

SHOW TABLES LIKE 'parent_table';

2

Create parent table first, then child

-- Parent table first
CREATE TABLE categories (
    id INT PRIMARY KEY
);

-- Then child with FK
CREATE TABLE products (
    id INT PRIMARY KEY,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

2. Match Column Types Exactly medium

FK columns must have identical types

1

Check column types match exactly

-- Wrong: INT vs INT UNSIGNED mismatch
CREATE TABLE parent (id INT UNSIGNED PRIMARY KEY);
CREATE TABLE child (parent_id INT, FOREIGN KEY (parent_id) REFERENCES parent(id));

-- Correct: both INT UNSIGNED
CREATE TABLE child (parent_id INT UNSIGNED, FOREIGN KEY (parent_id) REFERENCES parent(id));

2

Check existing column types

DESCRIBE parent_table;
DESCRIBE child_table;

3. Ensure Referenced Column is Indexed easy

Parent column needs PRIMARY KEY or UNIQUE index

1

Check for index on parent column

SHOW INDEX FROM parent_table;

2

Add index if missing

ALTER TABLE parent_table ADD INDEX (column_name);
-- Or make it primary key
ALTER TABLE parent_table ADD PRIMARY KEY (column_name);

4. Match Character Set and Collation medium

Both tables must use same charset

1

Check table charset

SHOW CREATE TABLE parent_table;
SHOW CREATE TABLE child_table;

2

Ensure both use same charset

ALTER TABLE child_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

🔗

Related Errors

5 related errors

MariaDB Error 1215: Foreign Key Constraint Failure