mysql error 150 with INNODB

Asked Aug 28 '15 at 15:53

Active Aug 28 '15 at 15:59

Viewed 53 times

I am trying to add this table..

CREATE TABLE playersByUser (
    userid INT,
    playername VARCHAR(255),
    ordering tinyint,
    CONSTRAINT FOREIGN KEY (userid) REFERENCES users(id)
) ENGINE INNODB;

I know the error has to do with the INNODB, but what can I look at or change to make this work?

My users table has the column id that is auto increment and a primary field.

users TABLE

users
CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `firstname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `lastname` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `phone_number` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `username` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `password` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `salt` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
 `joined` datetime NOT NULL,
 `group` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=93 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_c

edited Aug 28 '15 at 15:59

asked Aug 28 '15 at 15:53

Paul

3,348
5
32
76

1

please post users table ddl too – xdebug Aug 28 '15 at 15:55
1

What is the full text of the error? That will probably help more than the error number. – gen_Eric Aug 28 '15 at 15:55
Can you try removing the `ENGINE INNODB` part from your query? Usually it is understood at the settings level. Also, try to give us the entire error message. Even better, try googling the complete error message. – Sarath Chandra Aug 28 '15 at 15:56
Does this help: http://stackoverflow.com/q/14888593? Also `ENGINE INNODB` should be `ENGINE=InnoDB`. – gen_Eric Aug 28 '15 at 15:56
@RocketHazmat error message is "ERROR 1005 (HY000): Can't create table 'shbdn.playersByUser' (errno: 150)" – xdebug Aug 28 '15 at 15:57
engine=INNODB doesn't help. I'll post my users table – Paul Aug 28 '15 at 15:59
The full error is ... `#1005 - Can't create table 'example.playersByUser' (errno: 150)` – Paul Aug 28 '15 at 16:00
Not sure, but does `playersByUser.userid` need to be a key (primary or index) before making the `FOREIGN KEY`? – gen_Eric Aug 28 '15 at 16:01
@RocketHazmat No, the `FOREIGN KEY` constraint itself fills the index requirement. – Michael Berkowski Aug 28 '15 at 16:02
@MichaelBerkowski: Oh, ok. I wasn't sure. – gen_Eric Aug 28 '15 at 16:03
1

Wait, the `INT(11)` isn't the problem (that's the default display length). It's the mismatch between InnoDB and MyISAM engines. – Michael Berkowski Aug 28 '15 at 16:03
1

@Paul Do you have a reason that the `users` table must be `MyISAM`? You aren't using a FULLTEXT index on it, for example. – Michael Berkowski Aug 28 '15 at 16:05
To be honest, and please excuse me ignorance, I am not too sure what `MYISAM` OR `INNODB` mean or do. – Paul Aug 28 '15 at 16:07
1

@Paul They are the [table's storage engines](https://dev.mysql.com/doc/refman/5.1/en/storage-engines.html). They have somewhat different capabilities, but for enforcing FK's InnoDB is typically used. MyISAM does not actually enforce them – Michael Berkowski Aug 28 '15 at 16:09
So would I just change the users table to `INNODB` ? – Paul Aug 28 '15 at 16:09
1

@Paul That should do it, yes. – Michael Berkowski Aug 28 '15 at 16:10
How would I go about changing an existing table's engine? – Paul Aug 28 '15 at 16:11
1

To change the engine of an existing table. http://stackoverflow.com/a/1780111/541091 – Michael Berkowski Aug 28 '15 at 16:13
@MichaelBerkowski Thanks that worked perfectly! – Paul Aug 28 '15 at 16:25
It actually works if both are INNODB: http://www.sqlfiddle.com/#!9/5bc6c. – Gordon Linoff Aug 28 '15 at 16:29

mysql error 150 with INNODB

0 Answers0