Primary keys are fields that identify a unique row from a table. There cannot be two of the same value in the same table, and they cannot be null. Adding a primary key to a table that defines objects is almost a must as it will assure you that you will always be able to differentiate two rows by this field.

Another part that makes primary keys so attractive is their ability to set the primary key as an autoincremental numeric value; that is, you do not have to assign a value to the ID, and MySQL will just pick up the latest inserted ID and increment it by 1, as we did with our Unique trait. Of course, for this to happen, your field has to be an integer data type. In fact, we highly recommend that you always define your primary key as an integer, even if the real-life object does not really have this ID at all. The reason is that you should search a row by this numeric ID, which is unique, and MySQL will add some performance improvements that come by setting the field as a key.

Then, let's add an ID to our book table. In order to add a new field, we need to alter our table. There is a command that allows you to do this: ALTER TABLE. With this command, you can modify the definition of any existing field, add new ones, or remove existing ones. As we add the field that will be our primary key and be autoincremental, we can add all these modifiers to the field definition. Execute the following code:

mysql> ALTER TABLE book
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT 
    -> PRIMARY KEY FIRST;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

Note FIRST at the end of the command. When adding new fields, if you want them to appear on a different position than at the end of the table, you need to specify the position. It could be either FIRST or AFTER <other field>. For convenience, the primary key of a table is the first of its fields.

As the table customer already has an ID field, we do not have to add it again but rather modify it. In order to do this, we will just use the ALTER TABLE command with the MODIFY option, specifying the new definition of an already existing field, as follows:

mysql> ALTER TABLE customer
    -> MODIFY id INT UNSIGNED NOT NULL
    -> AUTO_INCREMENT PRIMARY KEY;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Let's imagine that we need to keep track of the borrowed books. The table should contain the borrowed book, who borrowed it, and when it was borrowed. So, what kind of data would you use to identify the book or the customer? Would you use the title or the name? Well, we should use something that identifies a unique row from these tables, and this "something" is the primary key. With this action, we will eliminate the change of using a reference that can potentially point to two or more rows at the same time.

We could then create a table that contains book_id and customer_id as numeric fields, containing the IDs that reference these two tables. As the first approach, it makes sense, but we can find some weaknesses. For example, what happens if we insert wrong IDs and they do not exist in book or customer? We could have some code in our PHP side to make sure that when fetching information from borrowed_books, we only displayed the information that is correct. We could even have a routine that periodically checks for wrong rows and removes them, solving the issue of having wrong data wasting space in the disk. However, as with the Unique trait versus adding primary keys in MySQL, it is usually better to allow the database system to manage these things as the performance will usually be better, and you do not need to write extra code.

MySQL allows you to create keys that enforce references to other tables. These are called foreign keys, and they are the primary reason for which we were forced to use the InnoDB table engine instead of any other. A foreign key defines and enforces a reference between this field and another row of a different table. If the ID supplied for the field with a foreign key does not exist in the referenced table, the query will fail. Furthermore, if you have a valid borrowed_books row pointing to an existing book and you remove the entry from the book table, MySQL will complain about it—even though you will be able to customize this behavior soon—as this action would leave wrong data in the system. As you can note, this is way more useful than having to write code to manage these cases.

Let's create the borrowed_books table with the book, customer references, and dates. Note that we have to define the foreign keys after the definition of the fields as opposed to when we defined primary keys, as follows:

mysql> CREATE TABLE borrowed_books(
    -> book_id INT UNSIGNED NOT NULL,
    -> customer_id INT UNSIGNED NOT NULL,
    -> start DATETIME NOT NULL,
    -> end DATETIME DEFAULT NULL,
    -> FOREIGN KEY (book_id) REFERENCES book(id),
    -> FOREIGN KEY (customer_id) REFERENCES customer(id)
    -> ) ENGINE=InnoDb;
Query OK, 0 rows affected (0.00 sec)

As with SHOW CREATE SCHEMA, you can also check how the table looks. This command will also show you information about the keys as opposed to the DESC command. Let's take a look at how it would work:

mysql> SHOW CREATE TABLE borrowed_books G
*************************** 1. row ***************************
       Table: borrowed_books
Create Table: CREATE TABLE `borrowed_books` (
  `book_id` int(10) unsigned NOT NULL,
  `customer_id` int(10) unsigned NOT NULL,
  `start` datetime NOT NULL,
  `end` datetime DEFAULT NULL,
  KEY `book_id` (`book_id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `borrowed_books_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`),
  CONSTRAINT `borrowed_books_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Note two important things here. On one hand, we have two extra keys that we did not define. The reason is that when defining a foreign key, MySQL also defines the field as a key that will be used to improve performance on the table; we will look into this in a moment. The other element to note is the fact that MySQL defines names to the keys by itself. This is necessary as we need to be able to reference them in case we want to change or remove this key. You can let MySQL name the keys for you, or you can specify the names you prefer when creating them.

We are running a bookstore, and even if we allow customers to borrow books, we want to be able to sell them. A sale is a very important element that we need to track down as customers may want to review them, or you may just need to provide this information for taxation purposes. As opposed to borrowing, in which knowing the book, customer, and date was more than enough, here, we need to set IDs to the sales in order to identify them to the customers.

However, this table is more difficult to design than the other ones and not just because of the ID. Think about it: do customers buy books one by one? Or do they rather buy any number of books at once? Thus, we need to allow the table to contain an undefined amount of books. With PHP, this is easy as we would just use an array, but we do not have arrays in MySQL. There are two options to this problem.

One solution could be to set the ID of the sale as a normal integer field and not as a primary key. In this way, we would be able to insert several rows to the sales table, one for each borrowed book. However, this solution is less than ideal as we miss the opportunity of defining a very good primary key because it has the sales ID. Also, we are duplicating the data about the customer and date since they will always be the same.

The second solution, the one that we will implement, is the creation of a separated table that acts as a "list". We will still have our sales table, which will contain the ID of the sale as a primary key, the customer ID as a foreign key, and the dates. However, we will create a second table that we could name sale_book, and we will define there the ID of the sale, the ID of the book, and the amount of books of the same copy that the customer bought. In this way, we will have at once the information about the customer and date, and we will be able to insert as many rows as needed in our sale_book list-table without duplicating any data. Let's take a look at how we would create these:

mysql> CREATE TABLE sale(
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> customer_id INT UNSIGNED NOT NULL,
    -> date DATETIME NOT NULL,
    -> FOREIGN KEY (customer_id) REFERENCES customer(id)
    -> ) ENGINE=InnoDb;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE sale_book(
    -> sale_id INT UNSIGNED NOT NULL,
    -> book_id INT UNSIGNED NOT NULL,
    -> amount SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    -> FOREIGN KEY (sale_id) REFERENCES sale(id),
    -> FOREIGN KEY (book_id) REFERENCES book(id)
    -> ) ENGINE=InnoDb;
Query OK, 0 rows affected (0.00 sec)

Keep in mind that you should always create the sales table first because if you create the sale_book table with a foreign key first, referencing a table that does not exist yet, MySQL will complain.

We created three new tables in this section, and they are interrelated. It is a good time to update the diagram of tables. Note that we link the fields with the tables when there is a foreign key defined. Take a look:

As you know, primary keys are extremely useful as they provide several features with them. One of these is that the field has to be unique. However, you can define only one primary key per table, even though you might have several fields that are unique. In order to amend this limitation, MySQL incorporates unique keys. Their job is to make sure that the field is not repeated in multiple rows, but they do not come with the rest of the functionalities of primary keys, such as being autoincremental. Also, unique keys can be null.

Our book and customer tables contain good candidates for unique keys. Books can potentially have the same title, and surely, there will be more than one book by the same author. However, they also have an ISBN which is unique; two different books should not have the same ISBN. In the same way, even if two customers were to have the same name, their e-mail addresses will be always different. Let's add the two keys with the ALTER TABLE command, though you can also add them when creating the table as we did with foreign keys, as follows:

mysql> ALTER TABLE book ADD UNIQUE KEY (isbn);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE customer ADD UNIQUE KEY (email);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Indexes, which are a synonym for keys, are fields that do not need any special behavior as do the rest of the keys but they are important enough in our queries. So, we will ask MySQL to do some work with them in order to perform better when querying by this field. Do you remember when adding a foreign key that MySQL added extra keys to the table? Those were indexes too.

Think about how the application will use the database. We want to show the catalog of books to our customers, but we cannot show all of them at once for sure. The customer will want to filter the results, and one of the most common ways of filtering is by specifying the title of the book that they are looking for. From this, we can extract that the title will be used to filter books quite often, so we want to add an index to this field. Let's add the index via the following code:

mysql> ALTER TABLE book ADD INDEX (title);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Remember that all other keys also provide indexing. IDs of books, customers and sales, ISBNs, and e-mails are already indexed, so there is no need to add another index here. Also, try not to add indexes to every single field as in doing so you will be overindexing, which would make some types of queries even slower than if they were without indexes!