CODEWITHSUNDEEP
mysqldatabasesqliteforeign-keysforeign-key-relationship
Aggressor
Aggressor

Reputation: 13551

Set Foreign Key Column In Child Table To Null If Parent Foreign Key Deleted

I have an SQLite database.

I have learned how to insert a foreign key and now I would like to do this: 1. Delete the row which contains the foreign key in the parent table 2. Have any other table which references that foreign key set to null.

I have read about cascading deletes but that seems to delete any row which had that foreign key. Instead, I want to just null the value in any table that has a column value referencing the foreign key.

Can you advise what I can do to do this and perhaps what the terminology is?

Upvotes: 2

Views: 1948

Answers (2)

Deepak
Deepak

Reputation: 66

ON DELETE SET NULL

Add this where you create foreign key constraint

Upvotes: 2

spencer7593
spencer7593

Reputation: 108370

For MySQL, it sounds like you want to declare your foreign key constraint 

ON DELETE SET NULL

Reference: http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

I'm not as familiar with sqllite, but ON DELETE SET NULL is mentioned in...

Reference: https://www.sqlite.org/foreignkeys.html#fk_actions

Upvotes: 4

Related Questions