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

Last Updated : 20 Jun, 2022

In SQL, there exists a concept of referential integrity. This means that a foreign key can take reference from the primary key of another table. There exists basically 3 anomalies in this concept. Here, we discuss about Deletion Anomaly. This means that if an entry is present in the foreign key column of the target table, then that entry cannot be deleted from the primary key column of the base table. This is illustrated below. For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table STUDENT_INFO inside the database GeeksForGeeks. This table has 3 columns namely ROLL_NO, STUDENT_NAME and BRANCH containing the roll number, name and branch of various students.

Query:

CREATE TABLE STUDENT_INFO(
ROLL_NO INT PRIMARY KEY,
STUDENT_NAME VARCHAR(10),
BRANCH VARCHAR(5)
);

Output:

Step 4: Describe the structure of the table STUDENT_INFO.

Query:

EXEC SP_COLUMNS STUDENT_INFO;

Output:

Step 5: Create a table STUDENT_MARKS inside the database GeeksForGeeks. This table has 3 columns namely ROLL_NO, SUBJECT and MARKS containing the roll number, subject and marks of various students. Here the ROLL_NO column acts as a foreign key referencing from the STUDENT_INFO table’s ROLL_NO column, which is the primary key for the STUDENT_INFO table.

Query:

CREATE TABLE STUDENT_MARKS(
ROLL_NO INT REFERENCES STUDENT_INFO(ROLL_NO),
SUNJECT VARCHAR(10),
MARKS INT
);

Output:

Step 6: Describe the structure of the table STUDENT_MARKS.

Query:

EXEC SP_COLUMNS STUDENT_MARKS;

Output:

Step 7: Insert 3 rows into the STUDENT_INFO table.

Query:

INSERT INTO STUDENT_INFO VALUES(1,'JIM','CSE');
INSERT INTO STUDENT_INFO VALUES(2,'TIM','ELE');
INSERT INTO STUDENT_INFO VALUES(3,'PAM','ECE');

Output:

Step 8: Display all the rows of the STUDENT_INFO table.

Query:

SELECT * FROM STUDENT_INFO;

Output:

Step 9: Insert 2 rows into the STUDENT_MARKS table.

Query:

INSERT INTO STUDENT_MARKS VALUES(1,'CPP',98);
INSERT INTO STUDENT_MARKS VALUES(2,'DBMS',89);

Output:

Step 10: Display all the rows of the STUDENT_MARKS table.

Query:

SELECT * FROM STUDENT_MARKS;

Output:

Step 11: Delete 1 row from the STUDENT_INFO table where ROLL_NO is 3. The entry for the ROLL_NO column here i.e. 3 does not exist in the target table i.e. STUDENT_MARKS.

Query:

DELETE FROM STUDENT_INFO WHERE ROLL_NO=3;

Note: The deletion is successful as the deletion anomaly is not being violated since the roll number 3 is absent in the target table STUDENT_MARKS.

Output:

Step 12: Delete 1 row from the STUDENT_INFO table where ROLL_NO is 1. The entry for the ROLL_NO column here i.e. 1 exists in the target table i.e. STUDENT_MARKS.

Query:

DELETE FROM STUDENT_INFO WHERE ROLL_NO=1;

Note – The deletion is not successful and a referential integrity error is thrown as the deletion anomaly is being violated since roll number 1 is present in the target table STUDENT_MARKS.

Output:

Step 13: Delete 1 row from the STUDENT_MARKS table where ROLL_NO is 1.

Query:

DELETE FROM STUDENT_INFO WHERE ROLL_NO=1;

Output:

Step 14: Delete 1 row from the STUDENT_INFO table where ROLL_NO is 1. The entry for the ROLL_NO column here i.e. 1 does not exist in the target table i.e. STUDENT_MARKS.

Query:

DELETE FROM STUDENT_INFO WHERE ROLL_NO=1;

Note – The deletion is successful as the deletion anomaly is not being violated since the roll number 1 is absent in the target table STUDENT_MARKS.

Output: