MySQL - 在非空字段中删除设置空的外键 [英] MySQL - Foreign key on delete set null in not null field

问题描述

这可能是一个微不足道的问题，但在涉及外键约束时我仍然有点笨拙，所以我想确定一下.

假设我有一个表 countries，其中包含字段 country_id (PK) 和 name，还有一个表 cities 包含字段 city_id (PK)、name 和 country_id (FK).

外键 cities.country_id 具有约束 ON DELETE SET NULL.据我了解，这意味着如果删除来自 countries 的记录，则 cities 中引用该已删除记录的 country_id 的任何记录都将具有country_id 字段设置为 NULL.

但是，如果 cities.country_id 具有属性 NOT NULL 怎么办?这会阻止外键约束正常工作吗?这样做是有道理的，但我只是想检查一下.

解决方案

如果将 ON DELETE SET NULL 设置为外键，则不允许将字段设置为 非空.

因此，您将无法在 CountryId<上创建或更改列为 NOT NULL 和 ON DELETE SET NULL 的表/p>

当我运行以下语句时:

CREATE TABLE `country` (`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,`name` VARCHAR(100) 默认为空，主键(`id`)) ;创建表`城市`(`id` int(10) unsigned NOT NULL AUTO_INCREMENT,`name` varchar(100) NOT NULL,`countryId` int(10) unsigned DEFAULT NOT NULL,主键(`id`)，KEY `FK_country` (`countryId`),约束 `FK_country` 外键 (`countryId`) 参考 `country` (`id`) ON DELETE SET NULL ON UPDATE SET NULL);

我在 MySQL 5.5 中得到的错误是:

Schema Creation Failed: 您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以获取在NOT NULL"附近使用的正确语法，主键(`id`)，KEY `FK_country` (`countryId`),第 4 行的 CONSTRAINT `':

This is probably a trivial question, but I'm still a little clumsy when it comes to foreign key constraints so I wanted to make sure.

Let's say I have a table countries with the fields country_id (PK) and name, and a table cities with the fields city_id (PK), name and country_id (FK).

The foreign key cities.country_id has the constraint ON DELETE SET NULL. As I understand it, this means that if a record from countries is deleted, any records in cities that reference that deleted record's country_id will have its country_id field set to NULL.

What if, however, cities.country_id has the attribute NOT NULL? Will this prevent the foreign key constraint from working properly? It would make sense that it does, but I just want to check.

解决方案

If you set ON DELETE SET NULL to your foreign key then it won't allow you to set the field as NOT NULL.

So you won't be able to create or alter the table with column as NOT NULL and ON DELETE SET NULL on CountryId

When I run the below statements:

CREATE TABLE `country` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ;

CREATE TABLE `city` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `countryId` int(10) unsigned DEFAULT NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_country` (`countryId`),
  CONSTRAINT `FK_country` FOREIGN KEY (`countryId`) REFERENCES `country` (`id`) ON DELETE SET NULL ON UPDATE SET NULL
);

And I got the error in MySQL 5.5 is:

Schema Creation Failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_country` (`countryId`),
  CONSTRAINT `' at line 4: 

这篇关于MySQL - 在非空字段中删除设置空的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！