Can't create table errno 150

Question

Here is my SQL script

CREATE TABLE tracks(
track_id int NOT NULL AUTO_INCREMENT,
account_id int,
track_name varchar(255),
track_path varchar(255),
track_art_path varchar(255),
track_desc text,
primary key(track_id),
FOREIGN KEY (account_id) REFERENCES accounts_profile(accnt_id)
)

I don't see any syntax errors. Everything looks fine. My Database Engine is innoDB. but how come I keep on receiving this error?

#1005 - Can't create table 'beatbeast.tracks' (errno: 150)

It's not showing what line where the error is.

0th question: is table `accounts_profile` exists? Are the exact types of `tracks.account_id` the same as `accounts_profile.accnt_id` ? — biziclop, Jul 01 '12 at 12:43
Please also post the structure of `accounts_profile`. If the data types of `account_id` and `accounts_profile.accnt_id` are not the same (such as one is unsigned), the FK will fail with errno 150. — Michael Berkowski, Jul 01 '12 at 12:44
@user962206 Not according to your create statement above. It is a signed int here, and if it's unsigned in `accounts_profile`, it must be unsigned here too — Michael Berkowski, Jul 01 '12 at 12:46
okay alright. I was going to select you as best answer but you just commented is there by any chance you can post your answer? — user962206, Jul 01 '12 at 12:48
http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 — scriptin, Jul 01 '12 at 12:49

score 2 · Answer 1 · answered Jul 01 '12 at 12:45

From the documentation:

If MySQL reports an error number 1005 from a CREATE TABLE statement, and the error message refers to error 150, table creation failed because a foreign key constraint was not correctly formed.

Check that the datatype of accounts_profile.accnt_id matches tracks.account_id exactly. Currently one is an int, so the other must also be an int.

Furhter, the documentation suggests to call:

SHOW ENGINE INNODB STATUS

after you get the error message for a more detailed explanation.

score 2 · Accepted Answer · answered Jul 01 '12 at 13:37

Errno 150 is generally the result of a mismatch between the exact data types of the main table's referenced column, and the referencing column. In your case, tracks.account_id is a signed INT, but the column it references accounts_profile.accnt_id is INT UNSIGNED. So you must create the tracks table using INT UNSIGNED for account_id as well:

CREATE TABLE tracks(
  track_id int NOT NULL AUTO_INCREMENT,
  account_id int UNSIGNED,
  track_name varchar(255),
  track_path varchar(255),
  track_art_path varchar(255),
  track_desc text,
  primary key(track_id),
  FOREIGN KEY (account_id) REFERENCES accounts_profile(accnt_id)
)

Can't create table errno 150

2 Answers2