MySQL:当 "ON DELETE"时如何仅将一个参数设置为 null

Question

我有一个数据库结构，其中菜单选项可能指向页面，也可能不指向页面:

menu( 'en', 'anypage') => page( 'en', 'anypage')
menu( 'en', NULL) => Nothing

底部MySQL代码可以正确地创建和更新，但不能正确地删除:我希望当删除页面时，menu.Link设置为NULL并且menu.Language保持不变。

FOREIGN KEY (Language, Link) REFERENCES page(Language, Link) 
    ON UPDATE CASCADE ON DELETE SET Link=NULL

但这当然行不通。

所以我的问题是:如何仅更新菜单。删除页面时链接到 NULL？

-- DROP TABLE FOR MULTIPLE TESTS
DROP TABLE IF EXISTS menu;
DROP TABLE IF EXISTS page;
DROP TABLE IF EXISTS language;


-- CREATE TABLES
CREATE TABLE language(
    Id CHAR(2) PRIMARY KEY,
    Name VARCHAR(20)
) ENGINE=innoDB;

CREATE TABLE page(
    Language CHAR(2) NOT NULL,
    Link VARCHAR(30) NOT NULL,
    PRIMARY KEY (Language, Link),
    FOREIGN KEY (Language) REFERENCES language(Id) 
        ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = InnoDB;

CREATE TABLE menu(
    Id INT PRIMARY KEY AUTO_INCREMENT,
    Language CHAR(2) NOT NULL,
    Link VARCHAR(30) DEFAULT NULL,
    FOREIGN KEY (Language) REFERENCES language(Id) 
        ON UPDATE CASCADE ON DELETE CASCADE
    /*, FOREIGN KEY (Language, Link) REFERENCES page(Language, Link) 
        ON UPDATE CASCADE ON DELETE SET LINK=NULL*/
) ENGINE=InnoDB;


-- INSERT FOR TESTS
INSERT INTO language (Id, Name) VALUES('en','English');
INSERT INTO page (Link, Language)VALUES('test', 'en');
INSERT INTO menu (Language, Link)VALUES('en', 'test');

UPDATE page SET Link='test2' WHERE Link='test';
DELETE FROM page WHERE Link='test2';

已编辑，基于@Bohemian 答案的最终解决方案:

/* Page deletion*/
DELIMITER //
CREATE TRIGGER PAGE_DELETE
AFTER DELETE ON page
FOR EACH ROW
BEGIN
    UPDATE menu SET
    menu.Link = NULL
    WHERE menu.Link = OLD.Link AND menu.Language = OLD.Language;
END;//

/* Page update*/
CREATE TRIGGER PAGE_UPDATE
AFTER UPDATE ON page
FOR EACH ROW
BEGIN
    UPDATE menu SET
    menu.Link = NEW.Link
    WHERE menu.Link = OLD.Link AND menu.Language = OLD.Language;
END;//

/* Menu creation*/
CREATE TRIGGER MENU_INSERT
BEFORE INSERT ON menu
FOR EACH ROW
BEGIN
    declare rowCount int default 1;
    if (NEW.Link IS NOT NULL) then
        SELECT count(Id) into rowCount FROM page 
        WHERE NEW.Link=page.Link AND NEW.Language=page.Language;
    end if; 

    if (rowCount=0 ) then
        signal sqlstate '23000' set message_text = 'Error: menu.Link must point to an existant page.';
    end if;
END;//

/* Menu update*/
CREATE TRIGGER MENU_UPDATE
BEFORE UPDATE ON menu
FOR EACH ROW
BEGIN
    declare rowCount int default 1;
    if (NEW.Link IS NOT NULL) then
        SELECT count(Id) into rowCount FROM page 
        WHERE NEW.Link=page.Link AND NEW.Language=page.Language;
    end if; 

    if (rowCount=0 ) then
        signal sqlstate '23000' set message_text = 'Error: menu.Link must point to an existant page.';
    end if;
END;//
DELIMITER ;

Answer 1

创建触发器:

DELIMITER //
CREATE TRIGGER PAGE_DELETE
AFTER DELETE ON PAGE
FOR EACH ROW
BEGIN
    UPDATE MENU SET
    PAGE_ID = NULL
    WHERE PAGE_ID = OLD.ID;
END;//
DELIMITER ;