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?

Solution 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

Solution 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.

Comments

[user3102872]

So when i say ON DELETE RESTRICT , the deleted row from the parent table won't get deleted from the other tables?

[Aram]

when it is set to RESTRICT, none of the rows(parent or child) will be deleted and you will see and error. if you want the parent row to be deleted regardless of the existence of the child rows, you should use NO ACTION then.

[user3102872]

So, Cascade means that when i delete the parent the child will get deleted, too. Restrict gives me an error when i try to delete. No Action doesn't change any thing in the child when i delete the parent. Set default sets the child to the default value when i delete the parent. Set null sets the child to null when the parent get deleted. am i right? Thank you very much

[Aram]

well, I just looked it up, looks like NO ACTION and RESTRICT are the same thing, and about the rest, you got it right.

[Kjetil S.]

ON DELETE NO ACTION (which is synonymous to ON DELETE RESTRICT on some RDBMSes) is the default action so it isn't necessary to write that. You can still delete children of course, you just have to do it explicitly with an extra DELETE FROM child_table before DELETE FROM parent_table, which I think is the best way.

[Savannah Madison]

restrict and drop comes in the context of dropping table too