Foreign key constraint is incorrectly formed sửa lỗi năm 2024

Encountering the MySQL error “Can’t create table x, error 150: the foreign key constraint incorrectly formed” while attempting to create a new table and assign a foreign key to an existing table can be frustrating. However, there are several practical solutions to address different causes of this issue. In this article, we will explore common reasons for this error and provide practical solutions, including sample queries, to help you overcome them.

Problem Description

The error message “Can’t create table x, error 150: the foreign key constraint incorrectly formed” can have multiple causes. Let’s examine these causes and their respective solutions.

• Collation and Character Encoding Mismatch A common cause of this error is a mismatch between the collation and character encoding of the involved tables. Collation defines the sorting and comparison rules for characters, while character encoding determines how characters are stored in binary form.

Solution To identify the collation and character encoding of the tables, execute the following queries:

SHOW CREATE TABLE existing_table; SHOW CREATE TABLE new_table;

The following parameters in the result can determine what character set and collation your table has:

DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Note: the parameters shown above can be used while creating the table. To change the collation of an existing table, use the following query:

ALTER TABLE existing_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• Type Mismatch between Referencing and Referenced Columns This error can occur when there is an inconsistency in the data types between the referencing and referenced columns. The column types must match exactly for the foreign key constraint to be created successfully.

Solution Check the data types of the columns involved in the foreign key relationship. Ensure that they match precisely, including the data length if applicable. Alter the column type, if needed, using a query such as:

ALTER TABLE referencing_table MODIFY COLUMN referencing_column INT;

• Missing Index on Referenced Column Another possible cause of the error 150 is the absence of an index on the referenced column. A foreign key constraint requires an index on the referenced column for proper functioning.

Solution Add an index to the referenced column using the following query:

CREATE INDEX idx_referenced_column ON referenced_table(referenced_column);

• Syntax Errors in Foreign Key Definition Syntax errors in the foreign key definition can lead to error 150. Common mistakes include referencing non-existent columns or tables, incorrect column order, or using incorrect syntax for ON DELETE or ON UPDATE actions.

Solution Verify the correctness of your foreign key definition. Here’s an example query to create a foreign key:

ALTER TABLE referencing_table ADD CONSTRAINT fk_reference FOREIGN KEY (referencing_column) REFERENCES referenced_table(referenced_column) ON DELETE CASCADE ON UPDATE SET NULL;

Ensure that the column and table names are accurate and exist, and double-check the syntax for ON DELETE and ON UPDATE actions.

• * Actions

  Automate any workflow  
  

  • Packages

    Host and manage packages  

  • Security Find and fix vulnerabilities
  • Codespaces Instant dev environments
  • Copilot Write better code with AI
  • Code review Manage code changes
  • Issues Plan and track work
  • Discussions Collaborate outside of code
• * GitHub Sponsors Fund open source developers
  • The ReadME Project

    GitHub community articles

• Pricing

Additional navigation options

Heading

Bold

Italic

Quote

Code

Link

Numbered list

Unordered list

Task list

Attach files

Mention

Reference 👍1 reacted with thumbs up emoji 👎1 reacted with thumbs down emoji 😄1 reacted with laugh emoji 🎉1 reacted with hooray emoji 😕1 reacted with confused emoji ❤️1 reacted with heart emoji 🚀1 reacted with rocket emoji 👀1 reacted with eyes emoji

You can’t perform that action at this time.