Understanding MySQL Primary Keys

Primary keys serve as unique identifiers for the records in a table, while foreign keys are used to link related tables together. When designing a set of database tables, it is important to specify which fields will be used for primary and foreign keys to clarify both in-table structure and inter-table relationships.

Primary Keys

You can specify a primary key for the table with the PRIMARY KEY constraint. In a well-designed database schema, a primary key serves as an unchanging, unique identifier for each record. If a key is declared as primary, this usually implies that the values in it will rarely be modified.

The PRIMARY KEY constraint can best be thought of as a combination of the NOT NULL and UNIQUE constraints because it requires values in the specified field to be neither NULL nor repeated in any other row. Consider the following example, which demonstrates by setting the numericAirportID field as the primary key for the airport table.

mysql> CREATE TABLE airport (
 -> AirportID smallint(5) unsigned NOT NULL,
 -> AirportCode char(3),
 -> AirportName varchar(255) NOT NULL,
 -> CityName varchar(255) NOT NULL,
 -> CountryCode char(2) NOT NULL,
 -> NumRunways INT(11) unsigned NOT NULL,
 -> NumTerminals tinyint(1) unsigned NOT NULL,
 -> PRIMARY KEY (AirportID)
 -> ) ENGINE=MYISAM;
Query OK, 0 rows affected (0.05 sec)

In this situation, because the AirportID field is defined as the primary key, MySQL won’t allow duplication or NULL values in that field. This allows the database administrator to ensure that every airport listed in the table has a unique numeric value, thereby enforcing a high degree of consistency on the stored data.

PRIMARY KEY constraints can be specified for either a single field or for a composite of multiple fields. Consider the following example, which demonstrates by constructing a table containing a composite primary key:

mysql> CREATE TABLE flightdep (
 -> FlightID SMALLINT(6) NOT NULL,
 -> DepDay TINYINT(4) NOT NULL,
 -> DepTime TIME NOT NULL,
 -> PRIMARY KEY (FlightID, DepDay, DepTime)
 -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.96 sec)

In this case, the table rules permit repetition of the flight number, the departure day, or the departure time, but not of all three together. Look what happens if you try:

mysql> INSERT INTO flightdep (FlightID, DepDay, DepTime)
 -> VALUES (511,1,'00:01');
Query OK, 1 row affected (0.20 sec)
mysql> INSERT INTO flightdep (FlightID, DepDay, DepTime)
 -> VALUES (511,2,'00:01');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO flightdep (FlightID, DepDay, DepTime)
 -> VALUES (511,1,'00:02');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO flightdep (FlightID, DepDay, DepTime)
 -> VALUES (511,1,'00:01');
ERROR 1062 (23000): Duplicate entry '511-1-00:01:00' for key 'PRIMARY'

Composite primary keys can come in handy when a record is to be uniquely identified by a combination of its attributes, rather than by only a single attribute.