So far, you’ve created the table classics and ensured that MySQL can search it quickly by adding indexes, but there’s still something missing. All the datainfo in the table can be searched, but there is no single unique key for each publication to enable instant accessing of a row. The importance of having a key with a unique value for each row will come up when we start to combine data from different tables.

So let’s go ahead and create a new column for this key. Now, bearing in mind that ISBNs or standardID are 13 characters long, you might think that the following command would do the job:

ALTER TABLE classics ADD isbn CHAR(13) PRIMARY KEY;

But it doesn’t. If you try it, you’ll get the error Duplicate entry for key 1. The reason is that the table is already populated with some data and this command is trying to add a column with the value NULL to each row, which is not allowed, as all values must be unique in any column having a primary key index. However, if there were no data already in the table, this command would work just fine, as would adding the
primary key index upon table creation.

In our current situation, we have to be a bit sneaky and create the new column without an index, populate it with data, and then add the index.

Populating the isbn column with data and using a primary key:

ALTER TABLE classics ADD isbn CHAR(13);
UPDATE classics SET isbn=’5051011157622′ WHERE year=’ 1998′;
UPDATE classics SET isbn=’5051865309826′ WHERE year=’1811′;
UPDATE classics SET isbn=’cls0135099′ WHERE year=’1990′;
UPDATE classics SET isbn=’6417832081437′ WHERE year=’1975′;
UPDATE classics SET isbn=’5052498183227′ WHERE year=’2020′;
ALTER TABLE classics ADD PRIMARY KEY(isbn);
DESCRIBE classics;

Creating the table classics with a primary key:
CREATE TABLE classics (
author VARCHAR(128),
title VARCHAR(128),
category VARCHAR(16),
year SMALLINT,
isbn CHAR(13),
INDEX(author(20)),
INDEX(title(20)),
INDEX(category(4)),
INDEX(year),
PRIMARY KEY (isbn)) ENGINE InnoDB;