mysql & Foreign Keys

If part of your create database code looks like this:

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT(5) NOT NULL AUTO_INCREMENT,
  `password` VARCHAR(128) NOT NULL,
  `salt` VARCHAR(50),
  `email` VARCHAR(320) NOT NULL,
  `zip` VARCHAR(10) NOT NULL,
  `role` INT(1) NOT NULL,
  `isactive` BOOL DEFAULT 1,
  `registeredon` DATETIME NOT NULL,
  `lastlogon` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS `userphones` (
  `userid` INT(5) NOT NULL,
  `phone` VARCHAR(15) NOT NULL,
  `description` VARCHAR(25) NOT NULL,
  KEY `fk_userphones_userid` (`userid`),
  CONSTRAINT `fk_userphones_userid`
  FOREIGN KEY (`userid`)
  REFERENCES `users` (`id`)
) ENGINE=INNODB;

And you try to load those statements in via a $mysqli->multi_query() command, mysql will fail with error 150, but it won't fail if you either add them sequentially using a foreach() and $mysqli->real_query or if you run them through phpmyadmin or similar.

Why?

Error 150 denotes a foreign key problem, which is triggered because the field names for the foreign key don't match up. The only way to get this error to go away on a multi_query is to make the `id` field in the `users` table `userid` instead, so the field names between tables match (which is not a requirement of a foreign key). Joy.