Database foreign key reference "On Update" and "On Delete" explained

Tips and Tricks
1

Database foreign key reference "On Update" and "On Delete" explained.

image
image672×528 22.7 KB

(you may need to click the Advanced button to view those extra options)

The following documentation was written by an LLM (Large Language Model; Artificial Intelligence) following my instructions. This topic was created to serve as future reference as it contains useful information.

In a (e.g. MariaDB) database, when you create a foreign key relationship between two tables, such as users.role_id referencing roles.id, you can define what should happen when the referenced data (in roles.id) is updated or deleted. The options "On Update" and "On Delete" allow you to control the behavior of the database in these scenarios.

The CASCADE option is one of several possible actions you can choose for both updates and deletes. Here's what it means:

"On Update CASCADE"

This means that if the value of the primary key in the referenced table (roles.id) is updated, then the corresponding foreign key in the referencing table (users.role_id) will also be updated automatically. In this case:

  • If the id in the roles table is changed, any corresponding role_id values in the users table will be automatically updated to reflect the new id.

"On Delete CASCADE"

This means that if a row in the referenced table (roles) is deleted, all rows in the referencing table (users) that reference that row will also be deleted. In this case:

  • If a row in roles is deleted, all users who have that particular role_id will also be deleted from the users table.

Other Possible Options

In addition to CASCADE, there are other options you could use for "On Update" and "On Delete":

  • RESTRICT: Prevents the update or delete operation if there are related records in the referencing table. For example, you can't delete a role from roles if there are still users assigned to that role in the users table.
  • NO ACTION: Similar to RESTRICT but allows the update/delete only if it doesn't violate referential integrity (i.e., if no related records exist).
  • SET NULL: Sets the foreign key column in the referencing table (users.role_id) to NULL if the corresponding row in the referenced table (roles.id) is deleted or updated.
  • SET DEFAULT: Sets the foreign key column in the referencing table to its default value if the referenced row is deleted or updated.

Example Scenario

Imagine you have a users table and a roles table where each user is assigned a role:

  • The roles table has columns like id and role_name (e.g., Administrator, Editor, Viewer).
  • The users table has columns like id, name, and role_id, which is a foreign key referencing roles.id.

With CASCADE set on "On Delete," if you delete the "Editor" role from the roles table, all users with the role "Editor" in the users table will also be deleted. Similarly, if the id of the "Administrator" role changes, all role_id values in the users table that point to "Administrator" will be updated accordingly.

By choosing other options like SET NULL or RESTRICT, you can change this behavior based on how you want to manage data consistency and referential integrity.

5 Likes