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 ?

<div class="code-mirror  language-sql" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> customers <span class="token punctuation">(</span>
  id <span class="token keyword">INTEGER</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
  name <span class="token keyword">TEXT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
 
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> orders <span class="token punctuation">(</span>
  id <span class="token keyword">INTEGER</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
  customer_id <span class="token keyword">INTEGER</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  product <span class="token keyword">TEXT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>customer_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> customers<span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
</div>

• Insert some sample data into the `customers` and `orders` tables ?

<div class="code-mirror  language-sql" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> customers <span class="token punctuation">(</span>id<span class="token punctuation">,</span> name<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'Alice'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> customers <span class="token punctuation">(</span>id<span class="token punctuation">,</span> name<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'Bob'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
 
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> orders <span class="token punctuation">(</span>id<span class="token punctuation">,</span> customer_id<span class="token punctuation">,</span> product<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'Widget'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> orders <span class="token punctuation">(</span>id<span class="token punctuation">,</span> customer_id<span class="token punctuation">,</span> product<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'Gadget'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> orders <span class="token punctuation">(</span>id<span class="token punctuation">,</span> customer_id<span class="token punctuation">,</span> product<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'Thingamajig'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</div>

• 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 ?

<div class="code-mirror  language-sql" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> students <span class="token punctuation">(</span>
  id <span class="token keyword">INTEGER</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
  name <span class="token keyword">TEXT</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
 
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> enrollments <span class="token punctuation">(</span>
  id <span class="token keyword">INTEGER</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
  student_id <span class="token keyword">INTEGER</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  course_id <span class="token keyword">INTEGER</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span>
  <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>student_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> students<span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>	
</div>

• Insert some sample data into the `students` and `enrollments` tables ?

<div class="code-mirror  language-sql" contenteditable="plaintext-only" spellcheck="false" style="outline: none; overflow-wrap: break-word; overflow-y: auto; white-space: pre-wrap;"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> students <span class="token punctuation">(</span>id<span class="token punctuation">,</span> name<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'Alice'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> students <span class="token punctuation">(</span>id<span class="token punctuation">,</span> name<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'Bob'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
 
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> enrollments <span class="token punctuation">(</span>id<span class="token punctuation">,</span> student_id<span class="token punctuation">,</span> course_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">101</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> enrollments <span class="token punctuation">(</span>id<span class="token punctuation">,</span> student_id<span class="token punctuation">,</span> course_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">1</span><span class="token punctuation">,</span> <span class="token number">102</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> enrollments <span class="token punctuation">(</span>id<span class="token punctuation">,</span> student_id<span class="token punctuation">,</span> course_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token number">2</span><span class="token punctuation">,</span> <span class="token number">103</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</div>

• 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.