table's primary key. In contrast, keys that are merely unique can be added purely for performance
reasons.
MySQL requires you to specify NOT NULL when creating a table with a given column specified as
PRIMARY KEY, even if it may allow a unique key to have null values (as in recent implementations).The choice of a primary key is very important in the design of a database; a primary key is the
fundamental piece of data that facilitates the joining of tables and the whole concept of a relational
database. This is why you must be careful to base your primary key on information that will always be
unique.
In the last example, you saw that lastname might not be a wise choice for a primary key. However, you
might create a more elaborate primary key by combining a number of fields to concoct something that
will be unique.Multiple-Column Primary Keys
It is possible to create a primary key as a multiple-column key.
You cannot do this in your CREATE TABLE statement, but must use the ALTER TABLE syntax, as
shown in the following:
ALTER TABLE customers ADD PRIMARY KEY (lastname,firstname);
Similarly, you could use partial indexing:
ALTER TABLE customers ADD PRIMARY KEY (lastname(6),firstname(6));To remove a primary key, you can use
ALTER TABLE customers DROP PRIMARY KEY;Synthetic Keys
You have the choice of making your key out of some unique pieces of real data or creating it as a separate
unique identifier, known as a synthetic or surrogate key.
You would make your key from the base data only if you were totally sure that it would never be null and
that a duplicate would never arise. For example, you might hope to base a key on the precise time of
someone's birth, or on their Social Security number, on the assumption that no two people would ever
be identical in these respects.However, these ideas are not perfect. People rarely know their time of birth precisely, and even if you
could record it to the second, there's no guarantee that a duplicate wouldn't arise at some time. Not all
countries in the world issue Social Security numbers, and in many areas it might be illegal to store them
without good reason, not to mention that customers may prefer not to tell you their numbers.It is not so difficult to find a unique key arising from the data in some scenarios. For example, if I were
creating a database of my extensive photo collection, I probably could use time as a unique identifier, as
long as I record the time I took each photo, and I never take photos more rapidly than the granularity of
the key—perhaps one per second.
Consider again the Customers table with a composite unique key, comprising say, last name, first
name, and address. This would be better, but still not ideal. It would fall down because people move
and even change their names. If a customer did this, the next time you tried to access the data using
your unique key, you might have problems finding him or her in the data. You are faced with the choice
of maintaining the customer's previous name or address as the key to his or her data, or updating your
key to contain the new data.
In trying to modify a key field, you would meet even worse problems. If you use the key to join a
Customers table with an Orders table, you would find yourself having to update the references to
customers in the Orders table too.You would run the risk of losing referential integrity. This is a serious situation in a relational database in
which important links between pieces of data become lost.
In this example, it would be better to create a unique customer identification number, what's known as a
synthetic key. A synthetic key is one that can be totally meaningless outside the context of the
database, and which we have "synthesized" purely for the purpose of finding a unique and convenient