Every table must and will have an attribute (or group of attributes together) that uniquely identifies every record. This attribute or group of attributes is called the primary key to the table. By “uniquely identify every record,” we mean that each record has a different value for that attribute or group of attributes. The primary key to our Person table is the “Name” attribute. We can never allow two different people to have the same name in this small database, although that is an unrealistic restriction in real life. That's why banks and other agencies identify you by social security number or an account number, which is guaranteed to be unique.

In our “Listens To” table, we need both attributes (person name and group name) to uniquely identify a row of data. People who like two bands are in there once for each band, so person name is not unique. And since several people can listen to the same band, music group names are duplicated too. But the combination of person name plus music group name is unique. So the primary key to our “Listens To” table is both these attributes.

As well as primary keys, many tables contain foreign keys. These are attributes in one table that are a primary key in some other table. The “Listens To” table has a foreign key of Name, which is the primary key for Person. The Person table does not contain any foreign keys. Although it has the Name attribute, that is only part of the key for the “Listens To” table, not the whole key. It's called a “foreign” key because it is not a key in this table, but for a table in some other distant place.

When you have a value of a foreign key, for instance, “Judith Brown” in the “Listens To” table, that value must also occur in the table for which it is the primary key. In other words, there must be a “Judith Brown” entry in the Person table. In fact, the purpose of keys is to be able to get to related data in other tables. Keys are how we navigate through the database. When the foreign key existence requirement is met, then the database is said to have referential integrity.

You keep referential integrity in a database by being careful about the data you remove. If you drop a customer account because of lack of activity, you must also drop all references to that customer in all tables in your database. The onus is on the programmer to keep referential integrity; the database cannot do it for you. If your database lacks referential integrity, you'll get funny results when you try to extract data from more than one table together.

There are two other forms of integrity that databases need: entity integrity and database integrity. If you don't have a value for some attribute, perhaps because you are still acquiring data for that table, there is a special value called “null” that can be assigned. Null doesn't mean zero. It means “no value has yet been assigned.” An expression involving null evaluates to null. We could use null in the age column, when someone does not wish to give us their age. We must never use null in any column that is part of a primary or foreign key. This is referred to as entity integrity. The entity integrity rule ensures that all our keys are always valid keys.

To understand transaction integrity, think of an update to a database that moves money from one account to another. There will probably be a row of data for the source account and the destination account. You need to write a statement that deducts the money from the first account, and a second statement that adds that sum to the second account. You want to be absolutely sure that either both statements are executed or neither of them are. You never want to be in a situation where the money was deducted but not paid in to the second account. If you have a way to group statements and ensure that all parts of the transfer occurs or none of it occurs, you can maintain transaction integrity.