0

I have a mysql table like below

id  tetangga1 tetangga2
1   null     null                       
2   1        3              
3   1        4           
4   4        5         
5   4        6         
6   6        null

i need to update either tetangga row's value to NULL when one of the row inside the same table is deleted, in example, if i delete the row 1, then the table would look like this 

id  tetangga1 tetangga2                       
2   NULL        3              
3   NULL        4           
4   4           5         
5   4           6         
6   6           NULL

explanation: row 1 will be deleted, row 2 and 3 tetangga1's value will be updated to null

how can i achieve it?

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Felix Candra
  • 55
  • 5

1 Answers1

1

You can do this with a foreign key relationship defined appropriately:

alter table t
    add constraint fk_t_tetangga1
        foreign key (tetangga1) references t(id)
        on delete set NULL;

This is actually better than a trigger. The database will maintain the relational integrity.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • if i were to add foreign key constraint, should i make a duplicate of that table or not? – Felix Candra Aug 06 '18 at 01:11
  • from what i read on https://stackoverflow.com/questions/16969060/mysql-error-1215-cannot-add-foreign-key-constraint on delete cannot be set null if the referenced key is primary key since primary key cannot be set to null – Felix Candra Aug 06 '18 at 01:29