Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL Query to Demonstrate Deletion Anomaly in Referential Integrity in a Table
Introduction
A SQL query is a request for data from a database. In the context of demonstrating a deletion anomaly in a table with referential integrity, a SQL query would be used to delete a record from the parent table and observe the impact on the related records in the child table.
To demonstrate a deletion anomaly, we can create two tables with a foreign key constraint, insert some sample data, and then use a `DELETE` statement to delete a record from the parent table. We can then use a `SELECT` statement to retrieve the data from the child table and observe any changes that have occurred as a result of the delete operation. This will show how the lack of referential integrity can cause a deletion anomaly.
Definition
In a database, referential integrity is a property that ensures that relationships between tables are maintained consistently. This means that if a record in a parent table (such as a customer) is deleted, any related records in a child table (such as orders placed by that customer) should also be deleted.
However, if the foreign key constraints are not properly set up, it is possible for a deletion anomaly to occur. A deletion anomaly occurs when a record in a parent table is deleted, and as a result, the related records in a child table become "orphaned" and are no longer accessible. This can cause problems with data integrity and can lead to inconsistencies in the database.
Example 1
Here is an example of how you might demonstrate a deletion anomaly in a table with referential integrity
SQL Query
Create two tables, `customers` and `orders`, with a foreign key constraint on the `customer_id` field in the `orders` table that references the `id` field in the `customers` table −
CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, product TEXT NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers(id) );
Insert some sample data into the `customers` and `orders` tables −
INSERT INTO customers (id, name) VALUES (1, 'Alice'); INSERT INTO customers (id, name) VALUES (2, 'Bob'); INSERT INTO orders (id, customer_id, product) VALUES (1, 1, 'Widget'); INSERT INTO orders (id, customer_id, product) VALUES (2, 1, 'Gadget'); INSERT INTO orders (id, customer_id, product) VALUES (3, 2, 'Thingamajig');
Delete a record from the `customers` table and observe the impact on the related records in the `orders` table −
DELETE FROM customers WHERE id = 1; SELECT * FROM orders;
The resulting output would show that the orders placed by the customer with an `id` of 1 (Alice) have been deleted as well, demonstrating the deletion anomaly caused by the lack of referential integrity.
To avoid this problem, you can set up foreign key constraints with the `ON DELETE CASCADE` option, which will automatically delete any related records in the child table when a record is deleted from the parent table.
Example 2
To demonstrate a deletion anomaly in a student table with referential integrity, you can follow these steps −
SQL Query
Create two tables, `students` and `enrollments`, with a foreign key constraint on the `student_id` field in the `enrollments` table that references the `id` field in the `students` table −
CREATE TABLE students ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE enrollments ( id INTEGER PRIMARY KEY, student_id INTEGER NOT NULL, course_id INTEGER NOT NULL, FOREIGN KEY (student_id) REFERENCES students(id) );
Insert some sample data into the `students` and `enrollments` tables −
INSERT INTO students (id, name) VALUES (1, 'Alice'); INSERT INTO students (id, name) VALUES (2, 'Bob'); INSERT INTO enrollments (id, student_id, course_id) VALUES (1, 1, 101); INSERT INTO enrollments (id, student_id, course_id) VALUES (2, 1, 102); INSERT INTO enrollments (id, student_id, course_id) VALUES (3, 2, 103);
Delete a record from the `students` table and observe the impact on the related records in the `enrollments` table −
DELETE FROM students WHERE id = 1; SELECT * FROM enrollments;
The resulting output would show that the enrollments for the student with an `id` of 1 (Alice) have been deleted as well, demonstrating the deletion anomaly caused by the lack of referential integrity.
To avoid this problem, you can set up foreign key constraints with the `ON DELETE CASCADE` option, which will automatically delete any related records in the child table when a record is deleted from the parent table.
Conclusion
A deletion anomaly can occur in a database table with referential integrity when a record in a parent table is deleted and as a result, the related records in a child table become "orphaned" and are no longer accessible.
250 Views
