All geek questions in one place

MySQL errno: 150 cannot create table

I know that there are many questions regarding SO regarding this error, but even applying what I have learned from them, I still get "cannot 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 checked that foreign keys are stored exactly under this name in the respective tables.
I checked that the corresponding data types match this query. I checked that the two existing tables use InnoDB and utf8.
I have added UNSIGNED NOT NULL to foreign keys.
I indexed two foreign keys in my tables. I originally used s_id and r_id, but heard that this might cause some problems. r_eid is also unique, so I switched to this. This is not a primary key.

What am I missing? Thanks!

LESSON LEARN: Carefully check all aspects of your data types!

+4
source share
3 answers

It turns out that the mapping is set to utf8-unicode-ci instead of utf8-general-ci. Fixed, and now it works.

0
source

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

+2
source

The problem is that the source and target fields must be the same in all aspects. For example, if the source field is "unsigned" off, and the target is "unsigned", you will get this error.

+1
source

Source: https://habr.com/ru/post/1398738/

More articles:


All Articles