Monday, 31 July 2017

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.

*************









No comments:

Post a Comment

SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...