0

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;
Jack Lee
  • 35
  • 6

0 Answers0