Let us take a closer look at MySQL error 1005 (hy000) and how to remove it with the support of our MySQL support team at Bobcares.

MySQL ERROR 1005: Can’t create table (errno: 150) (Foreign Key)

The example given below will demonstrate a case in which this error could occur.

CREATE TABLE main(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
);
CREATE TABLE other(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
main_id INT NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(main_id) REFERENCES main(id)
);

When attempting to make the table “other” reference the table “main” via the foreign key “main id,” an Error 150 will opn up.

Solution for MySQL error 1005 (hy000) :

The foreign key “main id” must be of the same type as the primary key it refers. In the example, “main id” in the table “other” has the type INT NOT NULL, whereas “id” in the table “main” has the type “INT UNSIGNED NOT NULL” as well as AUTO INCREMENT, but this isn’t a problem. Here’s a working example to make everything crystal apparent.

CREATE TABLE main(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id)
);
CREATE TABLE other(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
main_id INT UNSIGNED NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY(main_id) REFERENCES main(id)
);

To fix ‘MySQL ERROR 1005: Can’t create table (errno: 150),’ we only need to make sure that the foreign key is of the same type as the primary key.

Other methods

• Make sure to include ENGINE=InnoDB; in your CREATE TABLE – statement.
• Ensure that the InnoDB isin the activation stage on the MySQL server. To test this, use the following command: SHOW VARIABLES LIKE ‘have innodb’; if YES, then InnoDB is enabled.
• Examine the command for upper- and lowercase letters in table and field names.
• Check this not just on the table we wish to create, but also on the tables to which the foreign keys relate.
• Make sure that the linked tables are appropriately indexed.

Troubleshooting:

We can attempt SHOW WARNINGS, but we’ll learn more about the error by executing the following command as root:

mysql> SHOW engine innodb STATUS;

It returns a dump of the InnoDB’s activity. A user can gain access to the complete details by unfolding the complete log.

------------------------
LATEST FOREIGN KEY ERROR
------------------------
1001111 13:11:00 Error IN FOREIGN KEY CONSTRAINT OF TABLE my_data :
FOREIGN KEY(id2)
REFERENCES pk(test1):
Cannot find an INDEX IN the referenced TABLE WHERE the
referenced COLUMNS appear AS the FIRST COLUMNS, OR COLUMN types
IN the TABLE AND the referenced TABLE do NOT MATCH FOR CONSTRAINT.

While there is additional information, it is not always sufficient to remedy the problem, especially if the user is new to MySQL. The first thing to check is if the data types in the foreign key and main key columns match.

The most popular variant is to use an int unsigned data type for the main key column and an int data type for the foreign key column. The InnonDB Engine terminates this process. Naturally, anybody can repair it by modifying the data type of the foreign key to match the int unsigned data type.

[Need assistance with similar queries? We are here to help]

Conclusion

To sum up we have now seen how to remove the To conclude the MySQL error 1005 (hy000) with the support of our tech support team.