referential integrity in database management system relational model

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