Understanding Keys

Keys are special pieces of data that help to identify a row of information in a table. In database terms, an individual row is also called a record and is composed of individual fields that correspond to columns (Figure 3.1).

Figure 3.1. The layout of a database table, with labels.

There are two types of keys you will deal with: primary and foreign. A primary key is a unique identifier that has to abide by certain rules. They must

• Always have a value (they cannot be NULL).

• Have a value that remains the same (never changes).

• Have a unique value for each record in the table.

The best real-world example of a primary key is the U.S. Social Security number. Although I have heard stories of duplicate numbers being assigned, the principle is that each individual has a unique Social Security number and that the number never changes. Just as the Social Security number is an artificial construct used to identify people, you'll frequently find creating an arbitrary primary key for each table to be the best design practice.

The second type of key is the foreign key. A foreign key is the representation in Table B of the primary key from Table A. If you have a hollywood database with a movies table and a directors table, the primary key from directors would be linked as a foreign key in movies. You'll see better how this works as the normalization process continues.

Currently, MySQL only enforces foreign keys when using the InnoDB storage engine (see Chapter 4, "Creating a MySQL Database," for more information on the different storage engines) but generally ignores their existence otherwise. Hence, foreign keys in MySQL are more of a theoretical presence than a binding one, although this should change in later versions of the software. But from a design perspective, you should keep in mind the correlation between primary and foreign keys.

The accounting database is just a simple table as it stands, but to start off the normalization process, I'll want to identify or create the primary key (the foreign keys will come in later steps).

To establish a primary key:

1.

Look for any fields that meet the three tests for a primary key.

In this example, the only data that will always be unique, that will have a value, and whose value will never change should be the Invoice Number. Mark this field as the primary key using the (PK) notation (Table 3.2).

Table 3.2. The first step I take in modeling my database is to identify a primary keythe Invoice Number.

Accounting Database
Item	Key
Invoice Number	Primary (PK)
Invoice Date	n/a
Invoice Amount	n/a
Invoice Description	n/a
Date Invoice Paid	n/a
Client Information	n/a
Expense Amount	n/a
Expense Category & Description	n/a
Expense Date	n/a

2.

If no logical primary key exists, invent one.

Frequently you will need to create a primary key because no good solution presents itself. Even with Social Security numbers and book ISBNs (International Standardized Book Numbers)which ought to meet the criteriacreating a dummy field expressly to serve as the primary key is a solid idea.

Tips

• MySQL allows for only one primary key per table, although you can base a primary key on multiple columns. In such a case, the combination of the values of the multiple columns must abide by the three rules: it can never be NULL, can never change, and must be unique for each record.

• Ideally, your primary key should always be an integer, which results in better MySQL performance. This is another reason why Social Security numbers and ISBNs, both of which contain hyphens, would not be the best possible primary key (and ISBNs sometimes contain the letter x).