Can't create table (errno 150) Openshift's phpmyadmin

Question

Here's my schema:

-- Table 'users'
CREATE TABLE IF NOT EXISTS `users` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `password` varchar(100) COLLATE utf8_bin NOT NULL,
  `registrationDate` datetime NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS tasks (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  description TEXT,
  title TEXT,
  type TEXT,
  createdDate DATETIME NOT NULL,
  finishedDate DATETIME,
  user_id bigint(20) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT user_fk FOREIGN KEY (user_id) REFERENCES USERS(ID) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

I keep getting errno 150 when I try creating "tasks" table.

Looks very similar to http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150 — Puhlze, Aug 27 '14 at 03:27
it's all very similar, but it's not the same. Everything matches everything in my tables as far as I can see. — vlashel, Aug 27 '14 at 03:33
This works fine when I execute it in sqlfiddle. What is the full error message? — Robbert, Aug 27 '14 at 03:43
150 is the only error message I get, I execute this on openshift's phpmyadmin — vlashel, Aug 27 '14 at 03:49
@vlashel http://stackoverflow.com/questions/16227199/mysql-errno-150 — Vignesh Kumar A, Aug 27 '14 at 03:59

score 1 · Accepted Answer · answered Aug 27 '14 at 14:53

Looks like phpmyadmin on openshift is case sensitive, if you dig into the error you get this:

LATEST FOREIGN KEY ERROR
------------------------
140827 10:52:57 Error in foreign key constraint of table testing/tasks:
 FOREIGN KEY (user_id) REFERENCES USERS(ID) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1:
Cannot resolve table name close to:
(ID) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1

In the below code, notice that i changed USERS to users in the second table creation code after REFERENCES.
Looks like this code works:

CREATE TABLE IF NOT EXISTS `users` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `password` varchar(100) COLLATE utf8_bin NOT NULL,
  `registrationDate` datetime NOT NULL,
  PRIMARY KEY (`ID`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

CREATE TABLE IF NOT EXISTS tasks (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  description TEXT,
  title TEXT,
  type TEXT,
  createdDate DATETIME NOT NULL,
  finishedDate DATETIME,
  user_id bigint(20) NOT NULL,
  PRIMARY KEY (id),
  CONSTRAINT user_fk FOREIGN KEY (user_id) REFERENCES users(ID) ON DELETE CASCADE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1;

Can't create table (errno 150) Openshift's phpmyadmin

1 Answers1