One of those questions that can leave a Node.js developer slightly stunned. For some reason, SQL and an understanding of relational databases have become the Achilles’ heel of backend development in the JavaScript world.
Referential actions are rules that define what should happen to rows in a child table when a row in the parent table, referenced by a foreign key, is updated or deleted.
There are three main ways to control this, and now we’ll look at them with an example. Let’s imagine we have users and orders tables. In the orders table, there is a user_id field, which is a foreign key that links each order to a specific user.
If we try to delete a user with id = 1, and that user is still being referenced by rows in the orders table, the referential integrity mechanism will come into play. It determines whether the deletion is allowed and, if so, under what rules. Next, we’ll go through those rules one by one, and you’ll see that it’s actually very simple and easy to understand.
RESTRICT
If a row in users is referenced by at least one row in orders, we won’t be able to delete it. The only way to delete such a record is to first delete the related rows from orders, and only then will referential integrity allow us to delete the user.
CASCADE
The most dangerous rule, as it means that when you delete a row from the users table, all rows in orders that reference that user will also be deleted.
SET NULL
When a user is deleted from the users table, all orders that reference that user will have their foreign key (user_id) set to NULL. The same rules apply to UPDATE operations as well, but since they are used relatively rarely, interviewers will usually ask specifically about DELETE.