SQL Query to Demonstrate Updation Anomaly in Referential Integrity in a Table

Introduction

A referential integrity constraint ensures that a foreign key value in one table matches a primary key value in another table. This helps to maintain the consistency and accuracy of the data in a database by preventing the insertion of incorrect or invalid data.

However, if there is an update anomaly, the referential integrity constraint can be violated, which can lead to inconsistencies in the data. An update anomaly occurs when an update to a primary key value in a table causes multiple foreign key values in other tables to become incorrect.

To demonstrate an update anomaly in referential integrity, we can use an SQL query to update a primary key value in a table and show how this update can cause a referential integrity constraint to be violated in another table.

Definition

An SQL query is a request for data from a database. In the context of demonstrating an update anomaly in referential integrity, an SQL query would be used to update a primary key value in a table and show how this update can cause a referential integrity constraint to be violated in another table.

Example 1

Here is an example of how to set up a database with two tables, `employees` and `departments`, and demonstrate an update anomaly in referential integrity −

SQL Query

// Create the departments table
CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(255)
);

// Insert some data into the departments table
INSERT INTO departments (department_id, department_name)
VALUES (1, 'IT'), (2, 'HR'), (3, 'Sales');

// Create the employees table with a foreign key constraint
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  employee_name VARCHAR(255),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

// Insert some data into the employees table
INSERT INTO employees (employee_id, employee_name, department_id)
VALUES (1, 'John', 1), (2, 'Jane', 2), (3, 'Jack', 3);

Now suppose we want to update the `department_id` of the `HR` department from `2` to `4`. We can do this with the following `UPDATE` statement −

// Update the department_id of the HR department to 4
UPDATE departments
SET department_id = 4
WHERE department_id = 2;

This `UPDATE` statement updates the `department_id` of the `HR` department from `2` to `4` in the `departments` table. However, if there are any `employees` in the employee's table with a `department_id` of `2`, this update will cause a referential integrity constraint to be violated because there is no longer a department with a `department_id` of `2` in the `departments` table. This creates an update anomaly because the data in the `employees` table is now inconsistent with the data in the `departments` table.

To fix the update anomaly, we would need to update the foreign key values in the `employees` table to match the new primary key value in the `departments` table −

// Update the department_id of employees in the HR department to 4
UPDATE employees
SET department_id = 4
WHERE department_id = 2;

This ensures that the referential integrity constraint is satisfied and the data in both tables is consistent.

Example 2

Here is an example of how to set up a database with two tables, `students` and `courses`, and demonstrate an update anomaly in referential integrity −

SQL Query

// Create the courses table
CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  course_name VARCHAR(255)
);

// Insert some data into the courses table
INSERT INTO courses (course_id, course_name)
VALUES (1, 'Math'), (2, 'Physics'), (3, 'Biology');

// Create the students table with a foreign key constraint
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(255),
  course_id INT,
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

// Insert some data into the students table
INSERT INTO students (student_id, student_name, course_id)
VALUES (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Eve', 3);

Now suppose we want to update the `course_id` of the Physics course from `2` to `4`. We can do this with the following `UPDATE` statement −

// Update the course_id of the Physics course to 4
UPDATE courses
SET course_id = 4
WHERE course_id = 2;

This `UPDATE` statement updates the `course_id` of the `Physics` course from `2` to `4` in the `courses` table. However, if there are any `students` in the students table with a `course_id` of 2, this update will cause a referential integrity constraint to be violated, because there is no longer a course with a `course_id` of `2` in the `courses` table. This creates an update anomaly, because the data in the `students` table is now inconsistent with the data in the `courses` table.

To fix the update anomaly, we would need to update the foreign key values in the `students` table to match the new primary key value in the `courses` table −

// Update the course_id of students in the Physics course to 4
UPDATE students
SET course_id = 4
WHERE course_id = 2;

This ensures that the referential integrity constraint is satisfied and the data in both tables is consistent.

Conclusion

A referential integrity constraint ensures that a foreign key value in one table matches a primary key value in another table, helping to maintain the consistency and accuracy of the data in a database.