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
   198   199   200   201   202   203   204   205   206   207   208