Data integrity in relational databases

To ensure data integrity, relational databases offer you two facilities.

Field definitions

When tables are created, the data type of each field must be defined – whether it's a number, a string, or a date, for example. In addition, many databases (including Access) offer a wide range of further data-checking facilities; for example, you can require that the field be entered (not left blank), specify minimum and maximum values, specify the maximum length of a string, constrain a string to certain values, and so on.

Indices

You create an index for two reasons. One is to speed up searches – creating an index like this has no effect on the data itself. The other is to enforce data integrity rules.

Indices can be used to ensure that duplicates cannot be entered (such an index is called a unique index, because the items it indexes must all be unique). Indices may be created on several fields; for example, a unique index on {Rat, Session, Trial} fields would ensure that the combination of those three fields would have to yield a unique value.

Indices are often thought of as being involved in relationships between tables, particularly when such relationships are one-to-one or many-to-one. If you have one table that relates rats to their experimental groups, and another that contains the session-by-session data, the two tables might be linked using the Rat field (as in the example we used earlier). It would be usual for the Group table to have a unique index for the Rat field. As this is also the field or key used to look up individual records, it is often called the primary key. Nevertheless, the index is only involved in the relationship to ensure the uniqueness of this key and to speed up the process of searching data; it has nothing to do with the end results of looking up the data.