Page 203 - Informatics_Practices_Fliipbook_Class12
P. 203
ADD FOREIGN KEY (Dept_No) REFERENCES DEPARTMENT (Dept_No);
ALTER TABLE DEPARTMENT
ADD FOREIGN KEY (Mgr_Id) REFERENCES EMPLOYEE(ID);
Foreign Key: enforces the referential integrity in the database.
Handling Referential Integrity Constraints
Since Mgr_Id of DEPARTMENT referencing the ID of EMPLOYEE and Dept_No of EMPLOYEE referencing Dept_
No of DEPARTMENT, there are some restrictions in inserting the tuples in EMPLOYEE and DEPARTMENT tables. For
example, Suppose we wish to insert information about the Accounts department having Dept_No 1, located in
Noida. Assume that the manager of the department has not yet been identified. We can still insert this tuple using
the following INSERT statement:
INSERT INTO DEPARTMENT
VALUES (1, 'Accounts', 'Noida', NULL);
Suppose we wish to insert information about the Automobile department having Dept_No 5, located in
Jamshedpur, and having a manager whose ID is 10007. To insert a tuple into the DEPARTMENT table for the
Automobile department, let us execute the following INSERT statement:
INSERT INTO DEPARTMENT
VALUES (5, 'Automobile', 'Jamshedpur', 10007);
The DBMS will reject such an attempt to insert data in the DEPARTMENT table as it violates the foreign key constraint.
Recall that the foreign key constraint dictates that before insertion of tuple with VALUES (5, 'Automobile',
'Jamshedpur', 10007), a tuple must exist in the EMPLOYEE table having ID value 10007. However, at
this point, the EMPLOYEE table does not have a tuple having ID value 10007. So, while inserting a tuple for the
Accounts department, we must specify the value of attribute Mgr_Id equal to NULL as shown below:
INSERT INTO DEPARTMENT
VALUES (4, 'Automobile', 'Jamshedpur', NULL);
Likewise, we can insert some more tuples in the DEPARTMENT table. Finally, the DEPARTMENT table appears as Table
5.1.
Dept_No Dept_Name Location Mgr_Id
1 Accounts Noida NULL
2 Administration Delhi NULL
3 Home Goods Mumbai NULL
4 Automobile Jamshedpur NULL
5 Textile Mumbai NULL
Table 5.1: DEPARTMENT table after inserting some tuples with Mgr_Id set to NULL
Now, let us insert into the EMPLOYEE table a tuple for Taran Adarsh, whose ID is 10007 and belongs to
Textile department as follows:
INSERT INTO EMPLOYEE
VALUES (10007, 'Taran', 'Adarsh', 'M', 'B-76, CST Road, Kalina, Santacruz East',
'Mumbai', 400098, '1965-01-13', 70000, 5);
SQL: Working with Two Tables 189

