MySQL中如何设置外键在删除关联记录时自动更新为默认值

核心实现逻辑 #

你写的外键规则ON DELETE SET DEFAULT本身就完全匹配你的需求，不需要额外调整逻辑，只需要注意以下2个必现前提即可正常运行：

• 必须使用支持外键约束的数据库引擎：如果是MySQL环境，需要确认建表时使用的是InnoDB引擎，MyISAM引擎不支持外键约束规则，会导致ON DELETE SET DEFAULT不生效。
• 必须保证decks表中deck_number=1的「Unsorted」记录永久保留，不能删除，否则设置默认值时会触发外键关联不存在的报错。

修正后可直接运行的建表语句 #

CREATE TABLE decks (
    deck_number INT PRIMARY KEY AUTO_INCREMENT,
    deck_name VARCHAR(40)
) ENGINE=InnoDB;

CREATE TABLE mtg_cards (
    multiverse_id INT PRIMARY KEY,
    card_name VARCHAR (40),
    set_name VARCHAR (40),
    price DECIMAL (8,2),
    location INT DEFAULT 1,
    FOREIGN KEY(location) REFERENCES decks(deck_number) ON DELETE SET DEFAULT
) ENGINE=InnoDB;

-- DECKS初始化数据
INSERT INTO decks VALUES(1,'Unsorted');
INSERT INTO decks VALUES(2,'Prifddinas Tribe');
INSERT INTO decks VALUES(3,'Bosh Smasshhhh!!!');
INSERT INTO decks VALUES(4,'Best Served Cold');

-- 卡牌测试数据
INSERT INTO mtg_cards VALUES(420830,'Nath of the Gilt-Leaf','Commander 2016',NULL,2);

-- 测试删除卡组
DELETE FROM decks
WHERE deck_name = 'Prifddinas Tribe';

效果验证 #

执行完上述删除语句后，查询multiverse_id=420830的卡牌记录，会发现location字段已经自动更新为1，卡牌记录不会被删除，符合你的预期。

内容的提问来源于stack exchange，提问作者Tanchwa