Implementation of Referential Integrity Constraint Using Foreign keys

For Complete YouTube Video: Click Here

In this class, we will try the Implementation of Referential Integrity Constraint Using Foreign keys.

We have already discussed the concepts of referential integrity constraints.

Implementation of Referential Integrity Constraint Using Foreign keys

The image below is the relations in the database schema that we have obtained so far.

We have created the employee and department tables, but we didn’t implement the relationship.

We will start creating the employee table and department again with the referential integrity constraints using foreign keys.

First, we will implement the department table.

The command for the creation of the department table is as shown below.

CREATE TABLE department (Dname varchar(15) NOT NULL, Dnumber int NOT NULL PRIMARY KEY, Locations varchar(20));

Next, we will implement an employee table with the foreign key constraints, as shown below.

CREATE TABLE employee ( Fname varchar (15) NOT NULL, Minit varchar(1), Lname varchar(15) NOT NULL, SSN varchar(9) NOT NULL, Bdate date, Address varchar(30), Sex varchar(1), Salary float, Dno int NOT NULL, PRIMARY KEY (ssn), FOREIGN KEY (Dno) REFERENCES department(Dnumber));

In the above command, the FOREIGN KEY is a keyword in the employee table. Dno is a foreign key that references the Dnumber of the department table.

Now, we insert the data into the department table.

INSERT INTO department (Dname, Dnumber, Locations) values (‘Research’, 5, ‘Hyd, Vja’);

INSERT INTO department (Dname, Dnumber, Locations) values (‘Admin’, 1, ‘Mum’);

INSERT INTO department (Dname, Dnumber, Locations) values (‘Purchase’, 4, ‘Del’);

From the above insertions, we have created departments with numbers 5, 1, and 4.

Now, we insert the data into the employee table.

INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Dno) values (‘James’, ‘E’, ‘Borg’, 888665555, ’10-NOV-1937′, ‘430 Stone, Houston, TX’, ‘M’, 55000, 1);

INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Dno) values (‘Jennifer’, ‘S’, ‘Wallace’, 987654321, ’20-JUN-1941′, ‘291 Berry, Bellaire, TX’, ‘F’,43000, 4);

INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Dno) values (‘Franklin’, ‘B’, ‘Wong’, 333445555, ’08-DEC-1955′, ‘638 Voss, Houston, TX’, ‘M’, 40000, 5);

We will test the referential integrity constraint by inserting new employee details with Dno 2, as shown below.

INSERT INTO employee (Fname, Minit, Lname, SSN, Bdate, Address, Sex, Salary, Dno) values (‘Alicia’, ‘J’, ‘Zelaya’, 999887777, ’19-JAN-1968′, ‘3321 Castle, Spring, TX’, ‘F’, 25000, 2);

We cannot insert this row as department number 2 is not existing in the table to which this column is referencing.

The error message is as shown below.