MySQL error 150 during CREATE TABLE

Question

Hi all, I have one table with name tbl_groupmaster created with SQL as shown below:

create table tbl_groupmaster (
  tgm_groupid int(10) unsigned NOT NULL auto_increment,
  tgm_groupname varchar(50),
  tgm_groupdescription varchar(50),
  PRIMARY KEY (tgm_groupid)
)

and I am creating one more table with name tbl_groupmanager, using a foreign key relationship:

create table tbl_groupmanager (
  tgmgr_groupmangerid int(10) NOT NULL,
  tgm_groupid int(10),
  UserNamesID int(10),
  tgmgr_groupsize int(10),
  tgmgr_groupassigned_date datetime,
  tgmgr_grouplead_status enum ('active','inactive'),
  PRIMARY KEY (tgmgr_groupmangerid),
  FOREIGN KEY (tgm_groupid) REFERENCES tbl_groupmaster(tgm_groupid)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

But I am getting this error:

SQL Error: Can't create table '.\student\tbl_groupmanager.frm' (errno: 150)..

What is this? I am unable to identify my mistake. Please help me to resolve this. Thanks in advance.

Answer 1

Most probably, MyISAM is default engine in your database and hence tbl_groupmaster is MyISAM.

MyISAM does not support foreign keys.

Answer 2

Your foreign key is not the same datatype as the primary key it references. One is unsigned, one isn't.

Answer 3

The type of the foreign key has to be the same as the referenced key. Change tgm_groupid on table tbl_groupmanager to int(10) unsigned and it will work.