V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
XiiLii
V2EX  ›  MySQL

MySQL 里 FOREIGN KEY 的 CASCADE 、RESTRICT 选项有优先级吗?

  •  
  •   XiiLii · 2023-01-05 21:52:29 +08:00 · 809 次点击
    这是一个创建于 686 天前的主题,其中的信息可能已经有所发展或是发生改变。

    我有歌手、专辑和单曲三张表,关系如下:

    restrict_cascade.png

    如果删除歌手,专辑和单曲会被删除;

    但,专辑被删除前,单曲会阻止专辑被删除;

    现在我删除张三报错了(在 Django ORM 构建的这种关系是可以将张三删除的)

    DELETE FROM Artist WHERE id=1; # 无法删除 张三
    

    请问有没有大佬了解 MySQL 里外键的 CASCADE 、RESTRICT 选项有没有优先级?或其它什么机制?

    我测试的表结构及数据如下:

    CREATE TABLE Artist(# 歌手
    	id INT PRIMARY KEY,
    	NAME VARCHAR(10)
    )
    
    CREATE TABLE Album(# 专辑
    	id INT PRIMARY KEY,
    	artist VARCHAR(10),
    	artist_id INT,
    	FOREIGN KEY (artist_id)	REFERENCES Artist(id) 
    	ON DELETE CASCADE ON UPDATE CASCADE # 歌手被删除时,他的专辑跟随一起被删除
    )
    
    CREATE TABLE Song(# 单曲
    	id INT PRIMARY KEY,
    	NAME VARCHAR(10),
    	artist_id INT,
    	album_id INT,
    	FOREIGN KEY (album_id) REFERENCES Album(id) 
    	ON DELETE RESTRICT ON UPDATE RESTRICT,# 专辑被删除前,单曲阻止专辑被删除
    	FOREIGN KEY (artist_id) REFERENCES Artist(id) 
    	ON DELETE CASCADE ON UPDATE CASCADE# 歌手被删除时,单曲跟随一起被删除
    )
    
    INSERT INTO Artist VALUES (1, "张三"), (2, "李四"); # 添加两名歌手:张三、李四
    INSERT INTO Album VALUES (1, "光专辑", 1), (2, "夜专辑", 2); # 添加两张专辑:光专辑 属于 张三,夜专辑 属于 李四
    INSERT INTO Song VALUES (1, "太阳", 1, 1), (2, "月亮", 1, 2); # 添加两首单曲:太阳 属于 张三的光专辑,月亮 属于 夜专辑(但不属于李四的单曲,而是属于张三的单曲)
    
    lookStupiToForce
        1
    lookStupiToForce  
       2023-01-06 12:10:32 +08:00
    emmmmmm
    手头没有 mysql 环境,而且也不知道你 mysql 版本,所以没测试 mysql
    不过你确定删除张三会报错?按理来说不会的啊

    至少我在 pgsql 里没有复现( version 12 )

    另外感觉这不仅是 restrict 和 cascade 优先级的问题,而是 mysql 有没有检测到 restrict 的对象还有一层相关联的 cascade 的问题
    也就是说,如果 mysql 检测到专辑 cascade 了歌手,而专辑 restrict 的歌曲也 cascade 了歌手,那么对歌手的所有操作都应当顺畅地 cascade 到专辑和歌曲上,而不用管什么 restrict 和 cascade 的优先级
    如果你确定你报错了,说明你的 mysql 版本可能还不支持这种检测或者这种多层带 restrict 的 cascade 操作
    XiiLii
        2
    XiiLii  
    OP
       2023-01-08 02:28:48 +08:00
    @lookStupiToForce 是的,MySQL 8.0.24 报错:错误代码:1451
    Cannot delete or update a parent row: a foreign key constraint fails (`test1`.`song`, CONSTRAINT `song_ibfk_1` FOREIGN KEY (`album_id`) REFERENCES `album` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT)

    我之前用 Django ORM 可以成功删除,例子原型: https://docs.djangoproject.com/zh-hans/3.2/ref/models/fields/#django.db.models.RESTRICT

    经过我测试,Django ORM 中的 PROTECT 才是 MySQL 中的 RESTRICT
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   1351 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 23ms · UTC 17:40 · PVG 01:40 · LAX 09:40 · JFK 12:40
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.