mysql table is not creating

Question

I am getting error when I create table with foreign key

create table _users(_id int(20) unsigned NOT NULL AUTO_INCREMENT,
_user_fullname varchar(50)not null,
_user_username varchar(160) not null,
_user_password varchar(200) not null,_user_remember_me tinyint,
_user_email varchar(30),
_user_mobile varchar(15),
_user_age varchar(10)
,primary key(_id,_user_email,_user_mobile));

_users table created successfully..there were no error..

But When I want to create employee table :

CREATE TABLE employee ( _Id INT NOT NULL AUTO_INCREMENT,
_user_mobile VARCHAR(15) not null,
_name varchar(15),
_org varchar(10),
PRIMARY KEY (_Id),
foreign key (_user_mobile) references _users(_user_mobile));

Its showing error:

ERROR 1005 (HY000): Can't create table 'DB.employee' (errno: 150)

What am I doing wrong??

can you execute show create table _users and update question — Mahesh Madushanka, Jun 27 '16 at 11:59
Foreign key mismatch. The pk in users has three columns, but you're trying to add a fk to just one of them. — jarlh, Jun 27 '16 at 11:59
http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 should help. — I_am_Batman, Jun 27 '16 at 11:59
I'd have the id column alone as pk, and have separate unique constraints on _user_email and _user_mobile. Then you can have that fk. — jarlh, Jun 27 '16 at 12:06
Out of curiosity... isn't it extremely annoying to type an underscore before every single table and column name? — Álvaro González, Jun 27 '16 at 12:19

score 2 · Answer 1 · answered Jun 27 '16 at 12:30

Hey In this case you just need to do one thing , you just need to add index to the reference column of the user table and then run the create table for employee

ALTER TABLE `_users` ADD INDEX (`_user_mobile`);

After running above query just run the below query :-

CREATE TABLE `employee`(
  `_Id` INT(11) NOT NULL AUTO_INCREMENT,
  `_user_mobile` VARCHAR(15) NOT NULL,
  `_name` VARCHAR(15),
  `_org` VARCHAR(10),
  PRIMARY KEY (`_Id`),
  FOREIGN KEY (`_user_mobile`) REFERENCES `_users`(`_user_mobile`) );

In this way you will get rid of the error 1005 of mysql which says that you need to have index on the reference column of parent table.

score 1 · Answer 2 · answered Jun 27 '16 at 12:15

150 is a foreign key error:

C:\>perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

Getting the exact error message is very tricky. You need to run this query:

show engine innodb status

... and search in the output:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
160627 14:09:32 Error in foreign key constraint of table test/employee:
foreign key (_user_mobile) references _users(_user_mobile)):
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.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.

Once you know that, it'd be easy to add the missing index:

ALTER TABLE `_users`
ADD UNIQUE INDEX `_user_email` (`_user_email`);

But I wouldn't if I were you. It's weird to use mobile phone number as key. Instead, just simplify the primary key:

create table _users(_id int(20) unsigned NOT NULL AUTO_INCREMENT,
_user_fullname varchar(50)not null,
_user_username varchar(160) not null,
_user_password varchar(200) not null,_user_remember_me tinyint,
_user_email varchar(30),
_user_mobile varchar(15),
_user_age varchar(10)
,primary key(_id));

... and use in the linked table:

CREATE TABLE employee ( _Id INT NOT NULL AUTO_INCREMENT,
_user_id int(20) unsigned not null,
_name varchar(15),
_org varchar(10),
PRIMARY KEY (_Id),
foreign key (_user_id) references _users(_id));

Nuri Tasdemir · Answer 3 · 2016-06-27T12:15:05.397

0

The problem is in the foreign key part. If you remove that, table will be created without a problem.

If you need to use that foreign key, you need to use InnoDB as the storage engine of MySQL. InnoDB allows a foreign key constraint to reference a non-unique key as can be seen in here.

edited Jun 27 '16 at 12:15

answered Jun 27 '16 at 12:09

Nuri Tasdemir

9,720
3
42
67

mysql table is not creating

3 Answers3