Menu
In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT . SET DEFAULT : This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.
ON DELETE NO ACTION (which is the same as omitting the ON DELETE clause) will actively prevent deletion of a parent row if it is referenced by any child table, not passively allow it to be deleted without affecting child rows.
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted.
You can delete a foreign key constraint in SQL Server by using SQL Server Management Studio or Transact-SQL. Deleting a foreign key constraint removes the requirement to enforce referential integrity.
From the column_constraint page on MSDN:
ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
Specifies what action happens to rows in the table that is altered, if those rows have a referential relationship and the referenced row is deleted from the parent table. The default is NO ACTION.
So, you can elide ON DELETE NO ACTION if you like and it will work just the same.
NO ACTION means that nothing will happen when you delete from your Subject table to the Topic table. In that case, if there is a row in Topic for a given SubjectId you cannot delete from it without breaking referential integrity, so the Delete will be rolled back.
More from MSDN:
NO ACTION - The SQL Server Database Engine raises an error and the delete action on the row in the parent table is rolled back.
I'm going to suggest to you that while you can skip the on delete no action, it might not be in your best interests to do so. Having this specified in the table definition might prevent someone later from adding an on cascade delete because they saw that you intended for that not to happen. This is particularly true when you correctly script all database objects and put them in source control and the code reviewer would see that there was a difference and ask why it happened. All too often , people are too eager to add on delete cascade and destroy data that should have been kept (like financial records for a customer who is no longer valid). They do this because they get the error that doesn't let them delete and just want to get rid of it instead of realizing that this is saving them from a massive mistake. At least if you have the code for Delete No Action in your table script, future maintainers will see that this was intentional and not just that you forgot to set up cascade deletes. Of course if your dba does not allow cascading deletes (as many do not and for good reason!) then this isn't a potential problem, but specifying your intent is often a good thing for maintainability.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With