Referential integrity in DBMS, Define referential integrity, referential integrity examples
Referential
Integrity
Entity integrity and referential integrity are the
basic set of rules for the relational model. Referential integrity defines the relationship between tables (entity
sets).
This property is shown by a foreign key. Foreign key is an attribute whose values match with the
primary key values of related table.
Referential integrity – If there is a foreign
key value stored in a table (for example, A), then there must be a matching primary
key value stored in another table (for example, B) that is linked with the
foreign key table.
For each distinct non-null foreign key
value in a relational database, there must exist a matching primary key value
from the same domain.
Example
VENDOR (Vendor_Number, VName, VAddress, Contact_Number)
VENDOR_BILL (Bill_Number, Bill_Date, Vendor_Num)
Here, Vendor_Num of VENDOR_BILL table refers to the values
of Vendor_Number of VENDOR table. So,
Vendor_Number of
VENDOR is the Primary key
Vendor_Num of VENDOR_BILL
is the Foreign key
The following are the permitted values for Vendor_Num
of VENDOR_BILL table;
- Vendor numbers that are already stored in the VENDOR table’s Vendor_Number attribute.
- A NULL value.
|
Vendor_Number
|
VName
|
VAddress
|
Contact_Number
|
|
V101
|
AB Ltd
|
Chennai
|
8565957542
|
|
V103
|
SS Ltd
|
Bengaluru
|
9859621432
|
Table
1: VENDOR (primary key table)
|
Bill_Number
|
Bill_Date
|
Vendor_Num
|
|
B1
|
12-Jun-2017
|
V101
|
|
B2
|
10-Jul-2017
|
NULL
|
|
B5
|
12-Jul-2017
|
V102
|
Table
2: VENDOR_BILL (foreign key table)
Please observe from the above tables, that ‘V101’ is
permitted value. Null is also a permitted value. But ‘V102’ is not. It is due
to the reason that the value ‘V102’ is not stored in Vendor_Number of Vendor
table.
*************
Go to Important Keywords/Terms in DBMS page
Go to Advanced DBMS concepts page