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