CODEWITHSUNDEEP
mysqlsqlforeign-keysmysql-error-1005
Beatrice
Beatrice

Reputation: 99

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?

Upvotes: 3

Views: 4171

Answers (3)

Beatrice
Beatrice

Reputation: 99

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

Upvotes: 0

John Smith
John Smith

Reputation: 175

The problem is that the source and target fields need to be the same in every aspect. For example, if the source field has "unsigned" off, and the target has "unsigned" on, you will get this error.

Upvotes: 1

juacala
juacala

Reputation: 2235

For essentially all the causes of this error, here is an exhaustive resource for what causes errno 150 (and errno 121/other foreign key errors) in MySQL.

MySQL Foreign Key Errors and Errno 150

Upvotes: 3

Related Questions