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

