on delete cascade vs on update cascade with code examples

Introduction
In the database management system, when one table refers to another table using foreign keys, it is essential to keep referential integrity between them. Referential integrity ensures that no data is lost or duplicated when a record is deleted or modified. SQL provides two options to implement referential integrity: on delete cascade and on update cascade. In this article, we will compare the two options through code examples to help you understand the difference between them.

On Delete Cascade
Let's assume that we have two tables, students and courses. The students table has a foreign key reference to the courses table.

CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(50)
);

CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
course_id INT,
FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

In the above example, the on delete cascade option is set to the foreign key constraint. It means that when a course is deleted from the courses table, all the associated records in the students table will be automatically deleted. This option guarantees referential integrity, and no student record will reference a non-existent course.

For example, let's say that we have the following records in the courses and students tables.

courses
course_id course_name
1 Mathematics
2 Science

students
student_id name course_id
1 John 1
2 Mary 2
3 Jack 1

Now, if we delete the Mathematics course from the courses table,

DELETE FROM courses WHERE course_id = 1;

The on delete cascade option will delete all the associated records from the students table as well.

students
student_id name course_id
2 Mary 2

On Update Cascade
Similarly, we can use the on update cascade option to maintain referential integrity when a foreign key value is updated in the parent table.

Let's modify the courses table as follows.

CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(50)
);

CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
course_id INT,
FOREIGN KEY (course_id) REFERENCES courses(course_id) ON UPDATE CASCADE
);

Suppose that there are two courses, Mathematics and Science, and four students are enrolled in the Mathematics course.

courses
course_id course_name
1 Mathematics
2 Science

students
student_id name course_id
1 John 1
2 Mary 1
3 Jack 1
4 Jessica 1

If we update the course_id of the Mathematics course to 3,

UPDATE courses SET course_id = 3 WHERE course_id = 1;

The on update cascade option will update the foreign key value in the students table accordingly.

students
student_id name course_id
1 John 3
2 Mary 3
3 Jack 3
4 Jessica 3

Conclusion
In conclusion, on delete cascade and on update cascade are essential options in maintaining referential integrity between related tables. On delete cascade guarantees that all associated records are automatically deleted when the parent record is deleted, whereas on update cascade will update all the foreign key values in the dependent table when the parent record is updated. By knowing the differences between the two options, you can select the best option that suits your data modeling requirements.

On Delete Cascade

On delete cascade is one of the most useful features of the foreign key constraint. It is used to maintain the referential integrity of the database when certain records with foreign keys are deleted. When you use the on delete cascade option, it executes a delete operation on all related records of the table, which maintains the referential integrity of the database.

This option is particularly useful in situations where you want to delete a parent record along with all related records in child tables. This ensures that you do not end up with orphaned child records in your database, which can cause data integrity issues and contribute to database bloat.

On Update Cascade

On update cascade is similar to on delete cascade, but it is used to maintain the referential integrity of the database when a record with a foreign key is updated. When an on update cascade option is used, it executes an update operation on the related records of the table, which maintains the referential integrity of the database.

This option is useful in situations where you want to update the primary key of a record, which also happens to be the foreign key of a child record. By using the on update cascade option, you can ensure that the foreign key value in the child records is updated automatically, without having to manually update each child record.

Example Scenarios

On delete cascade and on update cascade are two powerful options that can be used in a wide range of scenarios. Let's explore some examples to understand how these options work.

Scenario 1: Deleting a User Account

Suppose you have two tables, users, and orders. The orders table has a foreign key constraint that references the users table. When a user account is deleted, you want all the related orders to be deleted as well. To achieve this, you can use the on delete cascade option.

CREATE TABLE users (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
email varchar(100) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE orders (
id int(11) NOT NULL AUTO_INCREMENT,
user_id int(11) NOT NULL,
product varchar(100) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

In this scenario, when you execute a delete operation on the users table, all related records of the orders table will be deleted automatically.

Scenario 2: Updating a Product ID

Suppose you have two tables, products, and order_items. The order_items table has a foreign key constraint that references the products table. When a product ID is updated, you want all the related order_items to be updated as well. To achieve this, you can use the on update cascade option.

CREATE TABLE products (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) NOT NULL,
price decimal(10,2) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE order_items (
id int(11) NOT NULL AUTO_INCREMENT,
order_id int(11) NOT NULL,
product_id int(11) NOT NULL,
quantity int(11) NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (product_id) REFERENCES products(id) ON UPDATE CASCADE
);

In this scenario, when you update the product ID in the products table, the order_items table's foreign key reference will be updated automatically. This ensures that no data is lost or duplicated when the primary key in the products table is updated.

Conclusion

On delete cascade and on update cascade are essential features of the foreign key constraint. They are used to maintain referential integrity between related tables in the database. Using these features can help you avoid data integrity issues and improve database performance.

Popular questions

1. What is on delete cascade, and how does it work?
   Answer: On delete cascade is a feature of the foreign key constraint in a database management system that ensures referential integrity between related tables. It executes a delete operation on all related records of the table when a record with a foreign key is deleted. This ensures that there are no orphaned child records in the database.

2. What is on update cascade, and how does it work?
   Answer: On update cascade is another feature of the foreign key constraint in a database management system that also ensures referential integrity between related tables. It executes an update operation on the related records of the table when a record with a foreign key is updated. This ensures that the foreign key value in the child records is updated automatically without manual intervention.

3. When would you use on delete cascade?
   Answer: You would use on delete cascade when you want to delete a parent record along with all related records in child tables. This ensures that referential integrity is maintained in the database, and there are no orphaned child records. For example, when deleting a user account, you can use the on delete cascade option to delete all the related orders as well.

4. When would you use on update cascade?
   Answer: You would use on update cascade when you want to update the primary key of a record, which also happens to be the foreign key of a child record. By using the on update cascade option, you can ensure that the foreign key value in the child records is updated automatically, without having to manually update each child record.

5. How can on delete cascade and on update cascade help maintain referential integrity in a database?
   Answer: On delete cascade and on update cascade are used to maintain referential integrity in a database by ensuring that the child records are deleted or updated automatically when a parent record is deleted or updated. This guarantees that no data is lost or duplicated when a record with a foreign key is deleted or updated, which ensures data integrity in the database.

Tag

Cascade.