SQL Query to Demonstrate Addition 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 exists basically 3 anomalies in this concept. Here, we discuss about Addition/Insertion Anomaly. This means that if an entry is absent in the primary key column of the base table, then it cannot be entered into this foreign key column of the target 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 5 rows into the STUDENT_NFO 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 1 row into the STUDENT_MARKS table. The entry for the ROLL_NO column here i.e. 1 already exists in the base table i.e. STUDENT_INFO.

Query:

INSERT INTO STUDENT_MARKS VALUES(1,'CPP',100);

Note: The insertion is successful as the insertion/addition anomaly is not being violated since the roll number 1 was already present in the base table STUDENT_INFO.

Output:

Step 10: Insert 1 row into the STUDENT_MARKS table. The entry for the ROLL_NO column here i.e. 4 does not exist in the base table i.e. STUDENT_INFO.

Query:

INSERT INTO STUDENT_MARKS VALUES(4,'DBMS',95);

Note: The insertion is not successful and a referential integrity error is thrown as the insertion/addition anomaly is being violated since the roll number 4 was absent in the base table STUDENT_INFO.

Output:

Step 11: Insert 1 row into the STUDENT_INFO table having value of ROLL_NO as 4.

Query:

INSERT INTO STUDENT_INFO VALUES(4,'KAREN','ME');

Output:

Step 12: Insert 1 row into the STUDENT_MARKS table. Now, the entry for the ROLL_NO column here i.e. 4 exists in the base table i.e. STUDENT_INFO.

Query:

INSERT INTO STUDENT_MARKS VALUES(4,'DBMS',95);

Note: The insertion is successful as the insertion/addition anomaly is not being violated since the roll number 4 is now present in the base table STUDENT_INFO.

Output:

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

Query:

SELECT * FROM STUDENT_MARKS;

Output: