📋

Description

This error signifies that MySQL cannot process a foreign key constraint definition due to an invalid syntax or logical inconsistency. It commonly occurs during table creation or modification when the foreign key's properties do not align with the referenced key or the referenced table/column is missing.

💬

Error Message

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

🔍

Known Causes

4 known causes

⚠️

Data Type Mismatch

The data type, length, or signedness of the foreign key column(s) does not exactly match the referenced primary or unique key column(s).

⚠️

Referenced Key Missing

The column(s) referenced by the foreign key in the parent table either do not exist or are not defined as a PRIMARY KEY or a UNIQUE KEY.

⚠️

Charset/Collation Discrepancy

For string columns, the character set and collation of the foreign key column(s) do not match those of the referenced key column(s).

⚠️

Column Count/Order Mismatch

The number or order of columns specified in the foreign key constraint does not match the number or order of columns in the referenced primary/unique key.

🛠️

Solutions

4 solutions available

1. Verify Column Data Types and Collation easy

Ensure foreign key and referenced columns have compatible data types and collation.

1

Identify the foreign key column and the referenced column (the column in the parent table).

2

Use `SHOW CREATE TABLE` for both tables to inspect their column definitions.

SHOW CREATE TABLE your_child_table;
SHOW CREATE TABLE your_parent_table;

3

Compare the `DATA_TYPE` and `COLLATION` for the foreign key column and the referenced column. They must match exactly or be compatible (e.g., `VARCHAR(255)` and `VARCHAR(255)` are compatible, but `INT` and `VARCHAR` are not).

4

If there's a mismatch, modify the child table's column to match the parent table's column. This might involve dropping and re-adding the foreign key constraint.

ALTER TABLE your_child_table MODIFY your_foreign_key_column INT;
-- Or for character sets/collations
ALTER TABLE your_child_table MODIFY your_foreign_key_column VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5

Re-create the foreign key constraint after ensuring data type and collation compatibility.

ALTER TABLE your_child_table ADD CONSTRAINT fk_name FOREIGN KEY (your_foreign_key_column) REFERENCES your_parent_table(your_referenced_column);

2. Ensure Referenced Column is Indexed easy

The referenced column in the parent table must have an index, usually a PRIMARY KEY or UNIQUE index.

1

Identify the referenced column in the parent table that the foreign key points to.

2

Check if the referenced column has an index by examining the `SHOW CREATE TABLE` output for the parent table.

SHOW CREATE TABLE your_parent_table;

3

If the referenced column is not indexed (and it's not the PRIMARY KEY, which is automatically indexed), create an index on it.

CREATE INDEX idx_referenced_column ON your_parent_table(your_referenced_column);

4

If the referenced column is intended to be unique, consider making it a UNIQUE index.

ALTER TABLE your_parent_table ADD UNIQUE INDEX uidx_referenced_column (your_referenced_column);

5

Once the index is in place, try re-creating the foreign key constraint.

ALTER TABLE your_child_table ADD CONSTRAINT fk_name FOREIGN KEY (your_foreign_key_column) REFERENCES your_parent_table(your_referenced_column);

3. Correctly Specify Foreign Key Constraint Syntax medium

Double-check the syntax used when defining the foreign key, especially column names and the constraint name.

1

Review the `ALTER TABLE` statement used to create the foreign key. Pay close attention to the column names and the constraint name.

ALTER TABLE your_child_table ADD CONSTRAINT fk_name FOREIGN KEY (your_foreign_key_column) REFERENCES your_parent_table(your_referenced_column) ON DELETE CASCADE ON UPDATE RESTRICT;

2

Ensure that `your_foreign_key_column` exists in `your_child_table` and `your_referenced_column` exists in `your_parent_table`.

3

Verify that the number of columns in the foreign key definition matches the number of columns in the referenced table definition (for multi-column foreign keys).

ALTER TABLE your_child_table ADD CONSTRAINT fk_name FOREIGN KEY (col1, col2) REFERENCES your_parent_table(ref_col1, ref_col2);

4

Ensure that the constraint name (`fk_name`) is unique within the database.

5

If you are defining the foreign key during table creation, ensure the syntax is correct within the `CREATE TABLE` statement.

CREATE TABLE your_child_table (
    id INT PRIMARY KEY,
    your_foreign_key_column INT,
    FOREIGN KEY (your_foreign_key_column) REFERENCES your_parent_table(your_referenced_column)
);

4. Remove and Re-add the Foreign Key Constraint easy

A simple re-creation can sometimes resolve transient definition issues.

1

Identify the name of the foreign key constraint you are trying to create or that is causing the error. You can find this using `SHOW CREATE TABLE your_child_table;`.

SHOW CREATE TABLE your_child_table;

2

Drop the existing foreign key constraint if it exists and is causing issues.

ALTER TABLE your_child_table DROP FOREIGN KEY existing_fk_name;

3

Re-create the foreign key constraint with the correct definition, ensuring all previous checks (data types, indexing) have been performed.

ALTER TABLE your_child_table ADD CONSTRAINT fk_name FOREIGN KEY (your_foreign_key_column) REFERENCES your_parent_table(your_referenced_column);

🔗

Related Errors

5 related errors

MySQL Error 1239: Incorrect Foreign Key Definition