mysqlsqlforeign-keysmysql-error-1005

MySQL errno: 150 can't create table


I know there are a lot of questions on SO regarding this error, but even applying what I've gleaned from those, I still get "can't create table (errno: 150)"

CREATE TABLE sch_results
(
id INT NOT NULL AUTO_INCREMENT,
s_id INT UNSIGNED NOT NULL,
r_eid VARCHAR(10) NOT NULL,
cohorts INT,
allstudents INT,
comments TEXT,
PRIMARY KEY (id),
FOREIGN KEY (s_id) REFERENCES scholarships (id),
FOREIGN KEY (r_eid) REFERENCES sch_recommenders (eid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I've checked that the foreign keys are both stored exactly under that name in their respective tables.
I've checked that their respective data types match this query. I've checked that the two existing tables are using InnoDB and utf8.
I added the UNSIGNED NOT NULL to the foreign keys.
I indexed the two foreign keys in their tables. Initially, I was using s_id and r_id, but heard this might cause some problems. r_eid is also unique, so I switched to that. It is not the primary key.

What am I missing?


Solution

  • Turns out the collation was set to utf8-unicode-ci instead of utf8-general-ci. Fixed it and it works now.