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

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 exist basically 3 anomalies in this concept. Here, we discuss about Updation Anomaly. This means that if an entry is present in the foreign key column of the target table, then that entry cannot be updated in 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: 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 6: Display all the rows of the STUDENT_INFO table.

Query:

SELECT * FROM STUDENT_INFO;

Output:

Step 7: 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 8: Describe the structure of the table STUDENT_MARKS.

Query:

EXEC SP_COLUMNS STUDENT_MARKS;

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: Update 1 row in the STUDENT_INFO table where ROLL_NO is 3 and set the ROLL_NO to 30. The entry for the ROLL_NO column here i.e. 3 does not exist in the target table i.e. STUDENT_MARKS.

Query:

UPDATE STUDENT_INFO SET ROLL_NO=30 WHERE ROLL_NO=3;

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

Output:

Step 12: Display all the updated STUDENT_INFO table.

Query:

SELECT * FROM STUDENT_INFO;

Output:

Step 13: Update 1 row from the STUDENT_INFO table where ROLL_NO is 2 and set the ROLL_NO to 20. The entry for the ROLL_NO column here i.e. 2 exists in the target table i.e. STUDENT_MARKS.

Query:

UPDATE STUDENT_INFO SET ROLL_NO=20 WHERE ROLL_NO=2;

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

Output: