Example of When to use “ON UPDATE CASCADE” in SQLite
Consider a scenario having two tables: employees and departments. The employees table contains a foreign key constraint referencing the departments table on the department_id column. Let’s ensure that when the department_id of a department changes, the corresponding department_id of all associated employees is updated accordingly.
Step 1: Create Tables
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name TEXT
);
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
employee_name TEXT,
department_id INTEGER,
FOREIGN KEY (department_id) REFERENCES departments(department_id) ON UPDATE CASCADE
);
Step 2: Insert Initial Data
-- Insert departments
INSERT INTO departments (department_id, department_name) VALUES (1, 'IT');
INSERT INTO departments (department_id, department_name) VALUES (2, 'HR');
-- Insert employees
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (101, 'John Doe', 1);
INSERT INTO employees (employee_id, employee_name, department_id) VALUES (102, 'Jane Smith', 2);
Step 3: View Initial Data
Let’s take a look at the initial data in both tables:
Departments Table
Employees Table
Step 4: Update Department Information
Now, let’s update the name of the IT department from “IT” to “Information Technology“. Since we have enabled “ON UPDATE CASCADE”, this update will automatically reflect in the employees table.
UPDATE departments SET department_name = 'Information Technology' WHERE department_id = 1;
Step 5: View Updated Data
After the update, let’s examine the data in both tables again:
Reflect Changes in the employees table as shown in below image:
We can see that when the department name was changed it updated correctly in the departments table. Additionally, the employee ID of Jane Smith, who belongs to the IT department was automatically updated. This demonstrates how “ON UPDATE CASCADE” maintains consistency by updating related data across tables.
This illustration displays the actuality of “ON UPDATE CASCADE” clause in structuring SQLite databases, as it facilitates the solving of problems of data management, and monitoring data integrity safety standards.
When to Use ON UPDATE CASCADE in SQLite
“ON UPDATE CASCADE” is an important feature in SQLite that simplifies data management and ensures data integrity by automatically propagating changes from a parent table to its related child tables. This feature plays a crucial role in maintaining consistency and reducing the need for manual updates in SQLite databases.
In this article, We will learn about When to use “ON UPDATE CASCADE” in SQLite by understanding with the help of examples and so on.
Contact Us