Solve ERROR 1005 due to improper operations

When using MySQL, errors such as ERROR 1005 (HY000): Can't create table may easily occur when the operation is not performed. It may take a long time for many webmasters to find out the cause of the problem. I hope this article will help webmasters and Mysql beginners.


MySQL official problems:


There is a group of [latest foreign key error] in which detailed descriptions of recent errors and solutions are provided.


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.

The Index containing the "referenced field" cannot be found in the "referenced table", or the two associated field types do not match)



The following are two examples:


Example 1:



Program code


Create table booktype

(

Btid int (5) unsigned zerofill auto_increment not null primary key,

Btname varchar (100) not null unique,

Btnote text

);


Create table books

(

Bid int (5) unsigned zerofill auto_increment not null primary key,

Bname char (30) not null,

Isbn char (50) not null,

Author char (30) not null,

Press text,

Summary text,

Bcount int not null default 0,

Btid int,

Foreign key (btid) references booktype (btid)

);



Error:



Program code


ERROR 1005 (HY000): Can't create table '. \ bookdata \ books. frm' (errno: 150)





The main problems and solutions are as follows:


In foreign key (btid) references booktype (btid), The btid in the books table does not match the type of the associated field set in the int and booktype tables. The btid in the books table is becoming: btid int (5) unsigned zerofill will not report errors. This is often forgotten when creating and modifying tables.



Example 2:


When creating a foreign key in MySQL (Alter table xxx add constraint fk_xxx foreign key), an ERROR is prompted, but only the very simple message is displayed: ERROR 1005 (HY000): Can't create table '. \ env_mon \ # sql-698_6.frm '(errno: 150 ). It cannot solve the problem at all.


Drop table if exists products;

Create table products (

Id int not null auto_increment,

Title varchar (100) not null,

Description text not null,

Image_url varchar (200) not null,

Price decimal (10, 2) not null,

Date_available datetime not null,

Primary key (id)

) Type = innodb;



Drop table if exists line_items;


Create table line_items (

Id int not null auto_increment,

Product_id int not null,

Quantity int not null default 0,

Unit_price decimal (10, 2) not null,


Constraint fk_items_product foreign key (product_id) references producets (id ),


Index (product_id)

Primary key (id)

) Type = innodb;



Error:


ERROR 1005: Can't create table



The main problems and solutions are as follows:


1. MySQL supports foreign key constraints and provides the same functions as other databases, but the table type must be InnoDB.

2. index must be added to the column of the table with a foreign key.