Cascading in database?


Someone please explain cascading in database and different levels of cascading? What do they mean and how to use them?


In an SQL database you can define a Foreign Key from one table to another. For example if you have a “students” table and a “teachers” table, you can have a “homeroom teacher” column in the students table, which holds the ID (primary key) of one the teachers in the teachers table. This is defined as a foreign key.

But, what happens if a teacher is deleted? Every student which referenced that teacher would now have an invalid value in their “homeroom teacher” column.

ON DELETE CASCADE tells the database that when the teacher is deleted, every student which referenced that teacher should also be deleted. Similarly, ON DELETE SET NULL simply sets the “homeroom teacher” value for each of these students to null, instead of deleting them. Another option is ON UPDATE CASCADE which says that if the ID of the teacher changes, then the referenced ID of that teacher’s students would change accordingly.