What is referential integrity? Explain with a suitable example.

55views

written 7.3 years ago by

This constraint can be specified between 2 relations and it is used to maintain consistence amongst the table. This constraint can be implemented by using Primary and foreign relationship. Foreign key must contain those values which are present in Primary key and if not, it must contain null value.

Employee

ID	Name	Salary	dno
1	A	50,000	10
2	R	40,000	10
3	S	32,000	20
4	R	45,000	30
5	J	75,000	null

Dept.

dno	dname
10	HR
20	Admin
30	Mkt

In the above example, DMO in employee relation is a foreign key that refers dno from Dept. relation which is primary key. Above relations can be created as follows:

Create table employee ( idnumber(3) , PRIMARY KEY , name VARCHAR(10), salary(7,2),dno number (2),REFERENCES, dept(dno)) ;

Create table dept (dno number (2), PRIMARY KEY dname, VARCHAR (10));

Referential integrity in ER model:- Let us consider entity E1 and E2 having Pk K1 and K2. Let R be the relationship between these 2 entities that must contain K1 and K2. (k1, k2) is a foreign key of R.

enter image description here

ADD COMMENT EDIT