MySQL Foreign Key Error: Can't create table

Question

I am trying to create two tables in MySQL database called class_members and history. The class_members table contains information about the members and is created as:

CREATE TABLE `class_members` (
  `id` int(11) NOT NULL,
  `username` varchar(65) NOT NULL DEFAULT '',
  `password` varchar(65) NOT NULL DEFAULT '',
  `email` varchar(65) NOT NULL,
  `mod_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username_UNIQUE` (`username`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `email_UNIQUE` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

The class_members tables is successfully created.

I am facing the problem when creating history table which contains foreign key reference to class_members.

The command which I used is as follows:

CREATE TABLE `History` ( 
   `history_id` INT(11) NOT NULL AUTO_INCREMENT, 
   `username` VARCHAR(65) NOT NULL DEFAULT '', 
   `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
   `ip` VARCHAR(20) NOT NULL,  
   PRIMARY KEY (`history_id`), 
   CONSTRAINT `fk_history_member` FOREIGN KEY (`username`) 
   REFERENCES `class_members` (`username`) 
   ON UPDATE CASCADE);

When I execute the above command I get this error:

ERROR 1005 (HY000): Can't create table 'testDB.History' (errno: 150)

I tried to find why I cannot read the History table (about ERROR 1005) but I cannot understand why

Following Up on Comments, I created a new database and tried creating using the above command and I still get the error. See Attached Pic

Updated (Solved)

I found why the error in the above screenshot occurs. To solve that we have to add ENGINE=InnoDB DEFAULT CHARSET=utf8 to the end of the History table creation command.

 CREATE TABLE `History` ( 
   `history_id` INT(11) NOT NULL AUTO_INCREMENT, 
   `username` VARCHAR(65) NOT NULL DEFAULT '', 
   `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
   `ip` VARCHAR(20) NOT NULL,  
   PRIMARY KEY (`history_id`), 
   CONSTRAINT `fk_history_member` FOREIGN KEY (`username`) 
   REFERENCES `class_members` (`username`) 
   ON UPDATE CASCADE)ENGINE=InnoDB DEFAULT CHARSET=utf8;

This works perfectly.

Didnt i just referenced that? Would appreciate if you could help rather than trying to get points — kcc__, Dec 31 '17 at 14:10
I'm not sure how I get points for flagging as a duplicate? If that posts helps you out, it does help or am I thinking wrong? But it appears that the targeted post is more or less a duplicate from the post you referenced. — Ivar, Dec 31 '17 at 14:13
I have googled and read all those and i am still unable to solve the problem that is why I thought to ask the question. Perhaps my syntax is wrong or i missed some concepts on foreign key. That is why i tried to put up the command — kcc__, Dec 31 '17 at 14:14
Both of your `CREATE TABLE` statements [work for me](http://rextester.com/KAASZ61400). I also see nothing wrong with the foreign key `username` definition. Please post a reproducible error. — Tim Biegeleisen, Dec 31 '17 at 14:19
@Tim i posted the exact error i got. Moreover when i run that link your provided I get this error: “Cannot delete or update a parent row: a foreign key constraint fails” — kcc__, Dec 31 '17 at 14:24
@kcc__ Really strange...I just re-ran it and got the error. Let me try to fix it. — Tim Biegeleisen, Dec 31 '17 at 14:24
@Andomar It doesn't work, I just tested it again. I have no explanation _why_ it does not work though :-( — Tim Biegeleisen, Dec 31 '17 at 14:34
@Tim Biegeleisen works for me mysql workbench 6.3.9, mysql 5.7 and command line — P.Salmon, Dec 31 '17 at 14:36
@P.Salmon Yeah, it looks correct...maybe this is just Rextester acting up again. The error message is inconsistent with what is being done (namely creating tables, no DML stuff). — Tim Biegeleisen, Dec 31 '17 at 14:37
@kcc_ can you tell us what tool you are using (mysqlworkbench, phpmyadmin,command line sql etc)? — P.Salmon, Dec 31 '17 at 14:41

Andomar · Accepted Answer · 2017-12-31T14:41:29.717

2

Your query works on rextester.

Make sure you don't already have an existing table called history. If I re-create the history table, I get:

Cannot delete or update a parent row: a foreign key constraint fails

You can reproduce this by running the rextester query once, then remove the drop table if exists history; line, and run it again.

If you try to create the table twice in the same batch, you get this instead:

Table 'history' already exists

edited Dec 31 '17 at 14:41

answered Dec 31 '17 at 14:38

Andomar

232,371
49
380
404

1

@TimBiegeleisen: Of course, you have to drop `history` before dropping `class_members` :) – Andomar Dec 31 '17 at 14:40
Nice catch +1 ... wouldn't have thought of this – Tim Biegeleisen Dec 31 '17 at 14:41
There was still error but I managed to solve it by added `ENGINE=InnoDB DEFAULT CHARSET=utf8` to the end of the table. – kcc__ Dec 31 '17 at 14:51

score 0 · Answer 2 · answered Dec 31 '17 at 14:39

I think your problem is from the origin of the index. Try the code this way;

CREATE TABLE `class_members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(65) NOT NULL DEFAULT '',
  `password` varchar(65) NOT NULL DEFAULT '',
  `email` varchar(65) NOT NULL,
  `mod_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username_UNIQUE` (`username`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `email_UNIQUE` (`email`),
    INDEX `index_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `History` ( 
  `history_id` INT(11) NOT NULL AUTO_INCREMENT, 
  `username` VARCHAR(65) NOT NULL DEFAULT '', 
  `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  `ip` VARCHAR(20) NOT NULL,  
  PRIMARY KEY (`history_id`), 
  CONSTRAINT `fk_history_member` FOREIGN KEY (`username`) 
  REFERENCES `class_members` (`username`) 
  ON UPDATE CASCADE);

For SQLFiddle Sample; Click Here

MySQL Foreign Key Error: Can't create table

2 Answers2