SQL CODE:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
DROP TABLE IF EXISTS `db`.`zip_codes` ;
CREATE TABLE IF NOT EXISTS `db`.`zip_codes` (
`zip` CHAR(5) NOT NULL COMMENT '',
PRIMARY KEY (`zip`) COMMENT '')
ENGINE = InnoDB;
DROP TABLE IF EXISTS `db`.`houses` ;
CREATE TABLE IF NOT EXISTS `db`.`houses` (
`house_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
`zip` CHAR(5) NOT NULL COMMENT '',
`user_id` INT(11) NOT NULL COMMENT '',
PRIMARY KEY (`house_id`) COMMENT '',
INDEX `fk_houses_users1_idx` (`user_id` ASC) COMMENT '',
INDEX `fk_houses_zip_codes1_idx` (`zip` ASC) COMMENT '',
CONSTRAINT `fk_houses_users1`
FOREIGN KEY (`user_id`)
REFERENCES `db`.`users` (`user_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_houses_zip_codes1`
FOREIGN KEY (`zip`)
REFERENCES `db`.`zip_codes` (`zip`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 24
DEFAULT CHARACTER SET = latin1;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
The error I am getting:
Executing SQL script in server
ERROR: Error 1005: Can't create table 'db.zip_codes' (errno: 150)
SQL Code:
CREATE TABLE IF NOT EXISTS `db`.`zip_codes` (
`zip` CHAR(5) NOT NULL COMMENT '',
PRIMARY KEY (`zip`) COMMENT '')
ENGINE = InnoDB
SQL script execution finished: statements: 4 succeeded, 1 failed
In one of the lines I noticed that it says INDEX fk_houses_zip_codes1_idx
(zip
ASC) COMMENT '', I took out the ASC part and it still produced the same error. I know the 1500 error comes up when the foreign key reference is incorrect or the attribute type isnt the same. I double checked all these reasons and nothing seems wrong. Does anyone have any idea what I am doing wrong?
EDIT: User table
DROP TABLE IF EXISTS `db`.`users` ;
CREATE TABLE IF NOT EXISTS `db`.`users` (
`user_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '',
ENGINE = InnoDB
AUTO_INCREMENT = 23
DEFAULT CHARACTER SET = latin1;