What kind of primary key should I use

See Also

The power of a relational database system such as MySQL comes from its ability to quickly find and bring together information stored in separate tables. In order to do this, each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. Once you designate a primary key for a table, to ensure uniqueness, MySQL will prevent any duplicate or Null values from being entered in the primary key fields.

There are three kinds of primary keys that can be defined in MySQL: Auto Increment, single-field, and multiple-field.

· Auto Increment primary keys

· Single-field primary keys

· Multiple-field primary keys

Auto Increment primary keys

An Auto Increment field can be set to automatically enter a sequential number as each record is added to the table. Designating such a field as the primary key for a table is the simplest way to create a primary key.

Single-field primary keys

If you have a field that contains unique values such as ID numbers or part numbers, you can designate that field as the primary key. If the field you select as primary key does have duplicate or Null values, MySQL won't set the primary key. You can run a Find Duplicates query to determine which records contain duplicate data. If you can't readily eliminate duplicate entries by editing your data, you can either add an Auto Increment field and set it as the primary key or define a multiple-field primary key.

Multiple-field primary keys

In situations where you can't guarantee the uniqueness of any single field, you may be able to designate two or more fields as the primary key. The most common situation where this arises is in the table used to relate two other tables in a many-to-many relationship. An example would be an inventory database that uses a field part number of two or more fields (part and subpart).

Note If you are in doubt about whether you can select an appropriate combination of fields for a multiple-field primary key, you should probably add an Auto Increment field and designate it as the primary key instead. For example, combining FirstName and LastName fields to produce a primary key is not a good choice, since you may eventually encounter duplication in the combination of these two fields.