Can't create table 'table_name' (errno: 121) - How to solve MySQL error: Unable to create table, error number: 121

Can't create table 'table_name' (errno: 121) - How to solve MySQL error: Unable to create table, error number: 121, specific code examples are required

When using a MySQL database, you sometimes encounter error number 121, which prevents the table from being created. This issue often causes frustration among developers because without a specific error message, it is difficult to find a solution. This article explains how to solve this problem and provides specific code examples.

First, let’s understand the cause of this error. Error number 121 is usually caused by foreign key constraints. When you try to create a foreign key in a table, if the table's primary key and foreign key definitions conflict, MySQL will report an error and display "Can't create table 'table_name' (errno: 121)". So, to fix this problem, we need to check and fix the foreign key constraints.

The following are the steps and code examples to solve this problem:

Step 1: Find the problematic foreign key constraint
First, we need to find out which table's foreign key constraint is causing the problem question. You can use the following command to find the problematic table:

SHOW ENGINE INNODB STATUS;

After running this command, you will see the results containing detailed debugging information. In this result, you need to find the line "Latest deadlock:", the specific error message will be listed below. In the error message, "Foreign key constraint is incorrectly formed" usually means that the foreign key constraint is causing the problem.

Step 2: Repair foreign key constraints
Once the problematic foreign key constraint is found, we need to modify the table structure to fix it. Here are some common fixes:

Method 1: Check whether the data types of the primary key and foreign key are consistent
The data types of the primary key and foreign key must be consistent, otherwise error number 121 will result. Make sure the primary key and foreign key have the same data type.

Sample code:

CREATE TABLE table1 (
    id INT PRIMARY KEY
);

CREATE TABLE table2 (
    id INT,
    table1_id INT,
    FOREIGN KEY (table1_id) REFERENCES table1(id)
);

Method 2: Check whether the fields of the foreign key and the reference table are consistent
The fields referenced by the foreign key must exist in the reference table, and the field types must match. Please ensure that the field names and types of the foreign key and reference table are consistent.

Sample code:

CREATE TABLE table1 (
    id INT PRIMARY KEY
);

CREATE TABLE table2 (
    table1_id INT,
    FOREIGN KEY (table1_id) REFERENCES table1(id)
);

Method 3: Check table creation order
If you encounter a situation where there are foreign key constraints between multiple tables, you need to ensure that the table you create The order is correct. Please create the tables in the correct order so that existing tables are referenced when creating foreign keys.

Sample code:

CREATE TABLE table1 (
    id INT PRIMARY KEY
);

CREATE TABLE table2 (
    id INT PRIMARY KEY,
    table1_id INT,
    FOREIGN KEY (table1_id) REFERENCES table1(id)
);

Step 3: Re-create the table
After completing the above repair steps, you can re-create the problematic table. Run the CREATE TABLE statement to create the table and ensure that no errors are reported again.

Summary:
When MySQL reports the error "Can't create table 'table_name' (errno: 121)", it is usually caused by foreign key constraints. You can solve this problem by checking whether the data types of the primary key and the foreign key are consistent, checking whether the fields of the foreign key and the reference table are consistent, and checking the order in which the tables are created. This article provides specific code examples that I hope will help you solve this problem.

The above is the detailed content of Can't create table 'table_name' (errno: 121) - How to solve MySQL error: Unable to create table, error number: 121. For more information, please follow other related articles on the PHP Chinese website!