What is Cascade and Restrict in DROP table SQL, in MS SQL Server?

Answer 1

A table can have object dependencies. Indexes, views, programs.

When you give a "drop table" command, it will not drop the table that has dependencies unless you include the "cascade" command in the statement. The database programming is written this way to make sure you know the table has dependent objects and makes you explicitly say you want to drop the table and all its dependencies.

You should be very careful with this command. If you find you need to restore this table, you'd also have to recreate the view and index and recompile the programs attached.

The best way to recover from this is if you have a tool that will put the create statement into a file, including all attached objects. That way, if you drop the table and need to recover, all you have to do is run the create command which will recreate in a minute versus a recovery that could take 30 minutes to hours dependent upon the availability of the data. After you run the create statement, make sure all objects are attached, test the view and make sure the programs are VALID.

Answer 2

While dropping a parent table , it is required to drop the corresponding child table also.

ie.,
Parent table cannot be deleted if there is any child for it. So the cascaded drop or delete helps in deleting the child and then the parent.

Answer 3

RESTRICT specifies that table should not be dropped if any dependencies (i.e. triggers, stored procedure, primary key, foreign key etc) exist. Therefore, if there are dependencies then error is generated and the object is not dropped.

CASCADE specifies that even if there dependencies go ahead with the drop. That means drop the dependencies first and then the main object. So if the table has stored procedures and keys (primary and secondary keys) they are dropped first and then the table is finally dropped.

Answer 4

When we are using Drop table in SQL the syntax is simple. Drop table table_name(CASCADE / RESTRICT) We use cascade to drop table although it have some dependencies just like triggers, stroeprocrdure, primarykey, foreignkey it will delete first. But if we use restrict a error message is shown on using of DROP if the table have relation Trigger, store procedure.