MyISAM and InnoDB foreign key conflicts during MySQL table Creation

In MySQL, a table of the InnoDB type uses a foreign key to reference the primary key in the MyISAM table. An error occurs during table creation!

 

For example, create a user table users

 

Create Table users

(

Id int not null primary key auto_increment,

Username varchar (32) not null,

Password varchar (32)

) Engine = MyISAM;

 

Then create the user information table userinfo. The foreign key references the user table users.

 

Create Table userinfo

(

Id int not null primary key auto_increment,

User_id int not null,

......,

Constraint fk_user_id foreign key (user_id) References users (ID)

) Engine = InnoDB;

 

The system reports the following error:

Error 1005 (hy000): Can't create table 'test. userinfo' (errno: 150)

 

Solution:

Disable the foreign key constraint before userinfo is created. After userinfo is created, enable the foreign key constraint again.

 

Set foreign_key_checks = 0; // disable the foreign key check.

 

Create Table userinfo

(

Id int not null primary key auto_increment,

User_id int not null,

......,

Constraint fk_user_id foreign key (user_id) References users (ID)

) Engine = InnoDB;

 

 

Set foreign_key_checks = 1; // enable the foreign key check.