Difference between CASCADE and RESTRICT? SQL DDL database

Question

Could anyone tell me what exactly CASCADE, and RESTRICT mean? It's in database systems subject to the DDL Part

And what if I don't write any of them in my ON DELETE statement?

Answer 1

The ON DELETE CASCADE and ON DELETE RESTRICT are the foreign key properties and you set them when you create the relationship between two tables.

If you set the relationship to be ON DELETE CASCADE, when you run a DELETE statement on a "parent" table, it will automatically DELETE all the corresponding rows from the "child" table. But the RESTRICT (which is the default foreign key relationship behavior) is when you try to delete a row from the "parent" table and there is a row in the "child" table with the same ID, it will fail, complaining about the existing child rows.

Either way, you don't need to mention anything in your DELETE clause.

I also wrote a blog post about the different rules for DELETE and UPDATE commands in more detail here:

SQL Server Foreign Key Update and Delete Rules | Koukia

Answer 2

There are three types of on delete associated with foreign key

1. On Delete Cascade: when data is removed from a parent table, automatically data deleted from child table (foreign key table).
2. On Delete set Null: when data is removed from a parent table, the foreign key associated cell will be null in a child table.
3. On Delete Restrict: when data is removed from a parent table, and there is a foreign key associated with child table it gives error, you can not delete the record.