MySQL: How to set to null only one of the parameters when "ON DELETE"

Question

I have a Database structure in which a menu option may point to a page or not:

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

The bottom MySQL code work correctly for creation and update, but not for deletion: I would like when a page is deleted, menu.Link is set to NULL and menu.Language stay unchanged.

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

But that of course does not work.

So my question is: How to update just the menu.Link to NULL on page deletion?

-- 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';

EDITED, final solution based on @Bohemian answer:

/* 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 ;

Instead of ON DELETE SET LINK=NULL, have u tried with ON DELETE SET NULL?? — Todor, Oct 07 '14 at 07:42
SET NULL does not compile, because menu.Language is defined as NOT NULL — Adrian Maire, Oct 07 '14 at 07:49

score 1 · Accepted Answer · answered Oct 07 '14 at 07:41

1

Create a trigger:

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 ;

answered Oct 07 '14 at 07:41

Bohemian

412,405
93
575
722

Do I need to create also a trigger for Update? Or there is a "Foreign Key" that does not check on deletion? – Adrian Maire Oct 07 '14 at 07:56
You should never update the key of a table and there is no valid requirement to do it, so no - you don't need one for update because you will never update the key. To that end, page should have a surrogate key - called ID and auto incremented, and the foreign key should use that, typically called "table"_ID, like `PAGE_ID`. – Bohemian Oct 07 '14 at 09:12
Well, yes and no: The complete sql code has an auto_increment id for page (I removed a lot of code to make the example simpler). But the menu must point to (lang+link) to allow faster queries: Most times I only need the "link" so to request the "menu" table is enough, otherwise I need to query also page and it is a huge table. – Adrian Maire Oct 07 '14 at 09:26
Sounds like premature optimisation. Get the design right and easy to use. You'll probably find the performance gain you are trying to achieve is not significant, yet causes all sorts of programming problems – Bohemian Oct 07 '14 at 12:19

score 0 · Answer 2 · answered Oct 07 '14 at 08:08

0

Here is how i managed to do it:

-- 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,
    KEY page_link (Link)
) 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 (Link) REFERENCES page(Link) 
        ON UPDATE CASCADE ON DELETE SET 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';
SELECT * FROM menu;

answered Oct 07 '14 at 08:08

Todor

15,307
5
55
62

It seem to work, can you explain how adding key(Link) allow "SET NULL" for the menu foreign key? – Adrian Maire Oct 07 '14 at 08:17
Oh, ok, I see, the foreign key is only for Link, but what happend if two language has same link? – Adrian Maire Oct 07 '14 at 08:20
reading this answer here: http://stackoverflow.com/questions/1749332/mysql-cant-create-table-errno-150, seems like its required to have index on the field, before place a foreign key. – Todor Oct 07 '14 at 08:22
about the the languages with the same link, u can always do: ALTER TABLE menu ADD UNIQUE KEY lang_link (Language, Link) – Todor Oct 07 '14 at 08:42
But that mean that a menu(fr, test) may point to page(en, test) – Adrian Maire Oct 07 '14 at 08:52
U cant prevent this and allow at the same time something like menu(fr, null) – Todor Oct 07 '14 at 09:54

MySQL: How to set to null only one of the parameters when "ON DELETE"

2 Answers2