3.3 Integrity Rules

The following are the integrity rules to be satisfied by any relation.

• No Component of the Primary Key can be null.

• The Database must not contain any unmatched Foreign Key values. This is called the referential integrity rule.

Q: Can the Foreign Key accept nulls?
A: Yes, if the application business rule allows this.

How do we explain this ?

Unlike the case of Primary Keys, there is no integrity rule saying that no component of the foreign key can be null. This can be logically explained with the help of the following example:

Consider the relations Employee and Account as given below.

Employee

Emp# EmpName EmpCity EmpAcc#
X101 Shekhar Bombay 120001
X102 Raj Pune 120002
X103 Sharma Nagpur Null
X104 Vani Bhopal 120003

Account

ACC# OpenDate BalAmt
120001 30-Aug-1998 5000
120002 29-Oct-1998 1200
120003 01-Jan-1999 3000
120004 04-Mar-1999 500

EmpAcc# in Employee relation is a foreign key creating reference from Employee to Account. Here, a Null value in EmpAcc# attribute is logically possible if an Employee does not have a bank account. If the business rules allow an employee to exist in the system without opening an account, a Null value can be allowed for EmpAcc# in Employee relation.

In the case example given, Cust# in Ord_Aug cannot accept Null if the business rule insists that the Customer No. needs to be stored for every order placed.

The next issue related to foreign key reference is handling deletes / updates of parent?

In the case example, can we delete the record with Cust# value 002, 003 or 005 ?

The default answer is NO, as long as there is a foreign key reference to these records from some other table. Here, the records are referenced from the order records in Ord_Aug relation. Hence Restrict the deletion of the parent record.

Deletion can still be carried if we use the Cascade or Nullify strategies.

Cascade: Delete/Update all the references successively or in a cascaded fashion and finally delete/update the parent record. In the case example, Customer record with Cust#002 can be deleted after deleting order records with Ord# 101 and 104. But these order records, in turn, can be deleted only after deleting those records with Ord# 101 and 104 from Ord_Items relation.

Nullify: Update the referencing to Null and then delete/update the parent record. In the above example of Employee and Account relations, an account record may have to be deleted if the account is to be closed. For example, if Employee Raj decides to close his account, Account record with Acc# 120002 has to be deleted. But this deletion is not possible as long as the Employee record of Raj references it. Hence the strategy can be to update the EmpAcc# field in the employee record of Raj to Null and then delete the Account parent record of 120002. After the deletion the data in the tables will be as follows:

Employee

Emp# EmpName EmpCity EmpAcc#
X101 Shekhar Bombay 120001
X102 Raj Pune 120002 Null
X103 Sharma Nagpur Null
X104 Vani Bhopal 120003

Account

ACC# OpenDate BalAmt
120001 30-Aug-1998 5000
120002 29-Oct-1998 1200
120003 01-Jan-1999 3000
120004 04-Mar-1999 500
Powered by Blogger.