This chapter is from the book
This chapter is from the book
This chapter is from the book
Keys
Keys are pieces of data that help to identify a row of information in a table (a row is also called a record). 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 (it 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 keys are foreign keys. Foreign keys are the representation of the primary key from Table A in Table B. If you have a movies database with a movie table and a director table, the primary key from director would be linked as a foreign key in movie. You'll see better how this works as the normalization process continues.
Currently, MySQL formally implements foreign keys only when using the InnoDB table type (see Chapter 11, "Advanced MySQL," for more information on the different table types) 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.
The accounting database is just a simple table as it stands, but to start off the normalization process, I'll want to ensure at least one primary key (the foreign keys will come in later steps).
To assign a primary key:
- Look for any fields that meet the three tests for a primary key.
- 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 Number)—which ought to meet the criteria—creating a dummy field expressly for being the primary key is a solid idea.
In this example, the only data that will always be unique, 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 (Figure 3.1).
)
Figure 3.1 The first step I took in normalizing my database was to create the initial primary key—the Invoice Number.