Do I need to specify ON DELETE NO ACTION on my Foreign Key?
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.