📋

Description

This error indicates a problem with the definition of a foreign key constraint during table creation or alteration. It means MariaDB cannot establish or modify the relationship between the child and parent tables as specified, often due to mismatches in column definitions or non-existent references.

💬

Error Message

Incorrect foreign key definition for '%s': %s

🔍

Known Causes

4 known causes

⚠️

Data Type or Length Mismatch

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

⚠️

Referenced Column Not Indexed

The column(s) referenced in the parent table are not defined as a PRIMARY KEY or a UNIQUE KEY, which is a requirement for a foreign key constraint.

⚠️

Non-Existent Parent Reference

The parent table or the specific column(s) referenced by the foreign key constraint do not exist in the database schema.

⚠️

Character Set/Collation Discrepancy

The character set or collation of the foreign key column(s) in the child table differs from that of the referenced column(s) in the parent table, especially for string types.

🛠️

Solutions

4 solutions available

1. Verify Column Data Types and Indexes easy

Ensure referenced columns have matching data types and are indexed in the parent table.

1

Identify the parent table and column(s) referenced by the foreign key. Then, identify the child table and the foreign key column(s) causing the error.

2

Compare the data types of the foreign key column(s) in the child table with the data types of the referenced column(s) in the parent table. They must be identical (e.g., both INT, both VARCHAR(255)).

DESCRIBE child_table;
DESCRIBE parent_table;

3

If data types do not match, alter the child table's column to match the parent table's column.

ALTER TABLE child_table MODIFY COLUMN foreign_key_column INT;
-- Replace INT with the correct data type.

4

Ensure that the referenced column(s) in the parent table have an index. Primary keys are automatically indexed. If it's not a primary key, create an index.

SHOW INDEX FROM parent_table;
-- If no index exists on the referenced column, create one:
CREATE INDEX idx_parent_ref ON parent_table (referenced_column);

5

Re-attempt to create or modify the foreign key constraint.

ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (foreign_key_column) REFERENCES parent_table (referenced_column);

2. Correct Foreign Key Syntax and Column Names easy

Double-check the syntax of your FOREIGN KEY definition and ensure all column names are correct.

1

Carefully review the `CREATE TABLE` or `ALTER TABLE` statement where the foreign key is defined. Pay close attention to the `FOREIGN KEY (...) REFERENCES ... (...)` syntax.

2

Verify that the column names within the parentheses for the foreign key are correct and exist in the child table.

CREATE TABLE child_table (
    id INT PRIMARY KEY,
    parent_id INT,
    -- ... other columns
    FOREIGN KEY (parent_id) REFERENCES parent_table (id)
);

3

Verify that the referenced table name and column name within the `REFERENCES` clause are correct and exist in the parent table.

CREATE TABLE child_table (
    id INT PRIMARY KEY,
    parent_id INT,
    -- ... other columns
    FOREIGN KEY (parent_id) REFERENCES parent_table (parent_id_column)
);

4

Ensure there are no typos, extra commas, or missing parentheses in the foreign key definition.

5

If you are modifying an existing table, use `ALTER TABLE` and ensure the syntax is correct.

ALTER TABLE child_table
ADD CONSTRAINT fk_child_parent
FOREIGN KEY (child_column) REFERENCES parent_table (parent_column);

3. Address Table Engine Compatibility medium

Ensure both tables involved in the foreign key relationship use a compatible storage engine (like InnoDB).

1

Check the storage engine of both the child and parent tables. Foreign key constraints are primarily supported by the InnoDB storage engine in MariaDB.

SHOW CREATE TABLE child_table;
SHOW CREATE TABLE parent_table;

2

If either table is not using InnoDB, convert it. **Warning:** This operation can be time-consuming for large tables and should be performed during a maintenance window.

ALTER TABLE child_table ENGINE=InnoDB;
ALTER TABLE parent_table ENGINE=InnoDB;

3

After ensuring both tables are using InnoDB, re-attempt to create or modify the foreign key constraint.

ALTER TABLE child_table ADD CONSTRAINT fk_name FOREIGN KEY (foreign_key_column) REFERENCES parent_table (referenced_column);

4. Resolve Constraint Naming Conflicts easy

If you're dropping and recreating constraints, ensure the new constraint name is unique.

1

If you've previously created a constraint with the same name or are trying to add a constraint that already exists implicitly, you might encounter issues.

2

Before adding a new foreign key, check if a constraint with that name already exists. You can list existing constraints.

SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'child_table' AND CONSTRAINT_TYPE = 'FOREIGN KEY';

3

If a constraint with the desired name already exists, either drop it first (if it's an old or incorrect one) or choose a different, unique name for your new constraint.

ALTER TABLE child_table DROP FOREIGN KEY existing_fk_name;
-- Or, choose a new name for your new constraint:
ALTER TABLE child_table ADD CONSTRAINT new_unique_fk_name FOREIGN KEY (foreign_key_column) REFERENCES parent_table (referenced_column);

🔗

Related Errors

5 related errors

MariaDB Error 1239: Incorrect Foreign Key Definition