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

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

i have database construction in menu alternative may point page or not:

menu( 'en', 'anypage') => page( 'en', 'anypage') menu( 'en', null) => nil

the bottom mysql code work correctly creation , update, not deletion: when page deleted, menu.link set null , menu.language remain unchanged.

foreign key (language, link) references page(language, link) on update cascade on delete set link=null

but of course of study not work.

so question is: how update menu.link null on page deletion?

-- drop table 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 tests insert language (id, name) values('en','english'); insert page (link, language)values('test', 'en'); insert menu (language, link)values('en', 'test'); update page set link='test2' link='test'; delete page link='test2';

edited, final solution based on @bohemian answer:

/* page deletion*/ delimiter // create trigger page_delete after delete on page each row begin update menu set menu.link = null menu.link = old.link , menu.language = old.language; end;// /* page update*/ create trigger page_update after update on page each row begin update menu set menu.link = new.link menu.link = old.link , menu.language = old.language; end;// /* menu creation*/ create trigger menu_insert before insert on menu each row begin declare rowcount int default 1; if (new.link not null) select count(id) rowcount page new.link=page.link , new.language=page.language; end if; if (rowcount=0 ) signal sqlstate '23000' set message_text = 'error: menu.link must point existant page.'; end if; end;// /* menu update*/ create trigger menu_update before update on menu each row begin declare rowcount int default 1; if (new.link not null) select count(id) rowcount page new.link=page.link , new.language=page.language; end if; if (rowcount=0 ) signal sqlstate '23000' set message_text = 'error: menu.link must point existant page.'; end if; end;// delimiter ;

create trigger:

delimiter // create trigger page_delete after delete on page each row begin update menu set page_id = null page_id = old.id; end;// delimiter ;

mysql