Foreign Key Constraints + need a new set of eyes

[WebMachine]

I’m working through a CodeIgniter 3 tutorial and tried to add the database tables to my database using the schema that they provided. However, I’m getting an Error 1215 “Cannot add a foreign key constraint”.

I’ve checked to make sure the data types matched. Could someone please take a look at this schema and see if they can see what is causing the issue? Thanks.

-- -----------------------------------------------------
-- Table `bootsshop_db`.`product_categories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_categories` (
  `category_id` INT NOT NULL AUTO_INCREMENT,
  `category_description` VARCHAR(95) NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  PRIMARY KEY (`category_id`))
ENGINE = InnoDB;
 
-- -----------------------------------------------------
-- Table `bootsshop_db`.`product_brands`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_brands` (
  `brand_id` INT NOT NULL AUTO_INCREMENT,
  `brand_description` VARCHAR(45) NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  PRIMARY KEY (`brand_id`))
ENGINE = InnoDB;
 
-- -----------------------------------------------------
-- Table `bootsshop_db`.`products`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
  `product_id` INT NOT NULL AUTO_INCREMENT,
  `product_name` VARCHAR(245) NULL,
  `product_category_id` INT NULL,
  `product_model` VARCHAR(45) NULL,
  `product_brand_id` INT NULL,
  `tag_line` VARCHAR(245) NULL,
  `features_description` TEXT NULL,
  `product_price` DECIMAL NULL DEFAULT 0,
  `qty_at_hand` INT NULL DEFAULT 0,
  `editorial_reviews` TEXT NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  PRIMARY KEY (`product_id`),
  INDEX `fk_products_product_categories_idx` (`product_category_id` ASC),
  INDEX `fk_products_product_brands1_idx` (`product_brand_id` ASC),
  CONSTRAINT `fk_products_product_categories`
    FOREIGN KEY (`product_category_id`)
    REFERENCES `bootsshop_db`.`product_categories` (`category_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_products_product_brands1`
    FOREIGN KEY (`product_brand_id`)
    REFERENCES `bootsshop_db`.`product_brands` (`brand_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
  
-- -----------------------------------------------------
-- Table `bootsshop_db`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `user_id` VARCHAR(15) NOT NULL,
  `user_name` VARCHAR(45) NULL,
  `email_address` VARCHAR(45) NULL,
  `password` VARCHAR(45) NULL,
  `suspended` BIT NULL DEFAULT 0,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  PRIMARY KEY (`user_id`))
ENGINE = InnoDB; 
 
-- -----------------------------------------------------
-- Table `bootsshop_db`.`product_colours`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_colours` (
  `record_id` INT NOT NULL AUTO_INCREMENT,
  `product_id` INT NULL,
  `colour` VARCHAR(75) NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  PRIMARY KEY (`record_id`),
  INDEX `fk_product_colours_products1_idx` (`product_id` ASC),
  CONSTRAINT `fk_product_colours_products1`
    FOREIGN KEY (`product_id`)
    REFERENCES `bootsshop_db`.`products` (`product_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
 
-- -----------------------------------------------------
-- Table `bootsshop_db`.`product_images`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_images` (
  `image_id` INT NOT NULL AUTO_INCREMENT,
  `product_id` INT NULL,
  `featured` BIT NULL DEFAULT 0,
  `image_path` VARCHAR(245) NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  PRIMARY KEY (`image_id`),
  INDEX `fk_product_images_products1_idx` (`product_id` ASC),
  CONSTRAINT `fk_product_images_products1`
    FOREIGN KEY (`product_id`)
    REFERENCES `bootsshop_db`.`products` (`product_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;
 
-- -----------------------------------------------------
-- Table `bootsshop_db`.`pages`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `pages` (
  `page_id` INT NOT NULL AUTO_INCREMENT,
  `meta_title` VARCHAR(45) NULL,
  `meta_description` VARCHAR(45) NULL,
  `meta_keywords` VARCHAR(45) NULL,
  `content` TEXT NULL,
  `created_from_ip` VARCHAR(45) NULL,
  `updated_from_ip` VARCHAR(45) NULL,
  `date_created` DATETIME NULL,
  `date_updated` DATETIME NULL,
  PRIMARY KEY (`page_id`))
ENGINE = InnoDB;
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

[r937]

i just ran your sql and it worked fine

can you isolate which FK is generating the error message?

[WebMachine]

I ran it again to find which FK was the problem and it worked this time for me. Sorry for the trouble - it must have been something I did wrong when I copied and pasted the code into my sql query box.