Null


Represents a value for a column that is currently unknown or is not applicable for this record.


A null can be taken to mean ‘unknown’. It can also mean that a value is not applicable to a particular record, or it could just mean that no value has yet been supplied. Nulls are a way to deal with incomplete or exceptional data. However, a null is not the same as a zero numeric value or a text string filled with spaces; zeros and spaces are values, but a null represents the absence of a value. Therefore, nulls should be treated differently from other values. For example, suppose it was possible for a branch to be temporarily without a manager, perhaps because the manager has recently left and a new manager has not yet been appointed. In this case, the value for the corresponding mgrStaffNo column would be undefined.



Entity integrity


In a base table, no column of a primary key can be null.

For example, as branchNo is the primary key of the Branch table, we should not be able to insert a record into the Branch table with a null for the branchNo column.



Referential integrity


If a foreign key exists in a table, either the foreign key value must match a candidate key value of some record in its home table or the foreign key value must be wholly null. branchNo in the Staff table is a foreign key targeting the branchNo column in the home (parent) table, Branch. It should not be possible to create a staff record with branch number B300, for example, unless there is already a record for branch number B300 in the Branch table. However, we should be able


to create a new staff record with a null in the branchNo column to allow for the situation where a new member of staff has joined the company but has not yet been assigned to a particular branch.



Back to Main