Avoid Misunderstanding ON DELETE NO ACTION

Relational databases often provide several options for handling actions when a referenced row in a foreign key relationship is deleted.
Among these options, ON DELETE NO ACTION can be a bit confusing, both in terms of its naming and its behavior.

Let's read my post to ensure that you understand it correctly.

What Are ON DELETE Options?

The ON DELETE clause in SQL allows you to define what should happen to the rows in a child table when the related row in the parent table is deleted. ON DELETE options include:

• CASCADE: Automatically deletes child rows when the parent row is deleted.
• SET NULL: Sets the foreign key value in child rows to NULL when the parent row is deleted.
• RESTRICT: Prevents deletion of the parent row if any child rows exist.
• NO ACTION: Similar to RESTRICT; the action is deferred until the end of the transaction.

Comparing ON DELETE NO ACTION and ON DELETE RESTRICT

ON DELETE NO ACTION and ON DELETE RESTRICT seem similar since both options ultimately prevent the deletion of the parent row if it has dependent rows in the child table. However, the difference i in the timing:

• ON DELETE NO ACTION: The restriction is enforced at the end of the transaction. If any other statements within the transaction resolve the issue (like deleting the child rows), the delete action can proceed without raising an error.
• ON DELETE RESTRICT: The restriction is enforced immediately, preventing the delete action as soon as it’s attempted, without waiting for the end of the transaction.

If you're using Golang, you can reference "ON DELETE NO ACTION" similarly to "defer RESTRICT".