错误1215:使用ON DELETE SET NULL时无法添加外键约束 [英] ERROR 1215: Cannot add foreign key constraint when using ON DELETE SET NULL

问题描述

我正在尝试在MySQL中创建以下表.第一个:

I'm trying to create the following tables in MySQL. The first one:

CREATE TABLE IF NOT EXISTS address(
  address_id INT NOT NULL AUTO_INCREMENT,
  address_region VARCHAR(10) NOT NULL,
  address_country VARCHAR(20) NOT NULL,
  address_city VARCHAR(30) NOT NULL,
  PRIMARY KEY(address_id))ENGINE = InnoDB;

我成功创建了它，但是当我尝试如下创建另一个表时

I created it successfully,but when I try to create another table as following

CREATE TABLE IF NOT EXISTS spot(
  spot_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  spot_address INT(11) NOT NULL,
  spot_name VARCHAR(50) NOT NULL,
  spot_desc VARCHAR(500) DEFAULT ' ',
  spot_speadd VARCHAR(100) NOT NULL,
  spot_viewtime INT DEFAULT 0,
  FOREIGN KEY(spot_address)
  REFERENCES address(address_id)
  ON DELETE SET NULL
  ON UPDATE SET NULL);

我得到一个错误:

错误1215(HY000):无法添加外键约束

ERROR 1215 (HY000): Cannot add foreign key constraint

为什么此create table语句失败?

Why is this create table statement failing?

推荐答案

您在spot表中的spot_address上具有NOT NULL约束，然后尝试在父级中删除或更新时将其设置为NULL表address.

You have NOT NULL constraint on spot_address in the spot table and then try to set it to NULL on delete or update in the parent table address.

这篇关于错误1215:使用ON DELETE SET NULL时无法添加外键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！