外键错误1215

Question

我收到了一个

错误代码1215不能添加外键约束。

当我将FOREIGN KEY (product_id) REFERENCES products(product_id)排除在代码之外时，它看起来是有效的，但是当我添加它时，我会收到错误。

CREATE SCHEMA `my_web_db`;

USE my_web_db;

DROP TABLE IF EXISTS USERS;
DROP TABLE IF EXISTS DOWNLOADS;
DROP TABLE IF EXISTS PRODUCTS;

CREATE  TABLE `my_web_db`.`USERS` (
  `user_id` INT NOT NULL ,
  `email_address` VARCHAR(100) NULL ,
  `first_name` VARCHAR(45) NULL ,
  `last_name` VARCHAR(45) NULL ,
   PRIMARY KEY (`user_id`) )
DEFAULT CHARACTER SET = utf8;

CREATE  TABLE `my_web_db`.`DOWNLOADS` (
  `download_id` INT NOT NULL ,
  `user_id` INT ,
  `download_date` DATETIME NULL ,
  `filename` VARCHAR(45) NULL ,
  `product_id` INT NOT NULL,
  PRIMARY KEY (`download_id`),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
)
DEFAULT CHARACTER SET = utf8;

CREATE  TABLE `my_web_db`.`PRODUCTS` (
  `product_id` INT NOT NULL ,
  `product_name` VARCHAR(45) NULL ,
  PRIMARY KEY (`product_id`)
)
DEFAULT CHARACTER SET = utf8;

Answer 1

我认为您正在使用ENGINE=myisam，您应该使用ENGINE=InnoDB创建外键引用。

添加了引擎类型。更新的SQL：

CREATE  TABLE `my_web_db`.`USERS` (
  `user_id` INT NOT NULL ,
  `email_address` VARCHAR(100) NULL ,
  `first_name` VARCHAR(45) NULL ,
  `last_name` VARCHAR(45) NULL ,
   PRIMARY KEY (`user_id`) ) ENGINE=InnoDB DEFAULT CHARACTER SET = utf8;

CREATE  TABLE `my_web_db`.`PRODUCTS` (
  `product_id` INT NOT NULL ,
  `product_name` VARCHAR(45) NULL ,
  PRIMARY KEY (`product_id`)
)ENGINE=InnoDB DEFAULT CHARACTER SET = utf8;

CREATE  TABLE `my_web_db`.`DOWNLOADS` (
  `download_id` INT NOT NULL ,
  `user_id` INT ,
  `download_date` DATETIME NULL ,
  `filename` VARCHAR(45) NULL ,
  `product_id` INT NOT NULL,
  PRIMARY KEY (`download_id`),
  FOREIGN KEY (user_id) REFERENCES users(user_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB DEFAULT CHARACTER SET = utf8;