Page 202 - Informatics_Practices_Fliipbook_Class12
P. 202

DBMS uses foreign keys to enforce the integrity of the database. For example, DBMS will disallow an attempt to insert
        a tuple in the EMPLOYEE table having a  Dept_No that is not present in the DEPARTMENT table. Using SQL, we can
        specify Dept_No as a foreign key in the table EMPLOYEE and indicate that it references the primary key Dept_No
        of the table DEPARTMENT as follows:
        FOREIGN KEY (Dept_No) REFERENCES DEPARTMENT (Dept_No)
        The revised  CREATE TABLE statement for the table  EMPLOYEE would appear as follows:

        CREATE TABLE EMPLOYEE
        (
            ID INT PRIMARY KEY,

            FName VARCHAR(20) NOT NULL,
            LName VARCHAR(20) NOT NULL,
            Gender CHAR(1) NOT NULL,
            Address VARCHAR(30),

            City VARCHAR(20),
            Pin_Code CHAR(6),
            DOB DATE,
            Salary INT NOT NULL,

            Dept_No SMALLINT,
            FOREIGN KEY(Dept_No) REFERENCES DEPARTMENT(Dept_No)
        );
        Recall that a foreign key should either refer to existing UNIQUE attribute (typically primary key) value of referenced
        table or should be NULL. An update in the UNIQUE attribute (typically primary key) will lead to a corresponding update
        in the table where this attribute is a foreign key. For example, suppose Hiten Oberoi—-an employee who recently
        joined the company as a department manager, complains that his Aadhar number has been shown wrongly in his
        payslip. In that case, we will have to correct his Aadhar number by updating the EMPLOYEE table.  Such an update in
        the EMPLOYEE table will trigger a correction in the DEPARTMENT table for the department he manages. We can use
        the following CREATE TABLE statement for creating the DEPARTMENT table:

        CREATE TABLE DEPARTMENT (
            Dept_No SMALLINT PRIMARY KEY,
            Dept_Name VARCHAR(25) NOT NULL,
            Location VARCHAR(30),

            Mgr_Id INT UNIQUE
        );#DEPARTMENT
        Now, we are faced with the following situation:

        We want to specify in the description of the EMPLOYEE table that Dept_No of EMPLOYEE references Dept_No
        of DEPARTMENT. But for this purpose, the DEPARTMENT table must already exist in the database; else SQL will
        generate an error.

        We also want to specify in the description of the DEPARTMENT table that Mgr_Id of DEPARTMENT references the
        ID of EMPLOYEE, But for this purpose, the EMPLOYEE table must already exist in the database.
        To resolve the above conflict, we first create the tables without specifying foreign key constraints and then alter them
        by including the foreign key constraints. as shown below:

        ALTER TABLE EMPLOYEE
          188  Touchpad Informatics Practices-XII
   197   198   199   200   201   202   203   204   205   206   207