MySQL will not allow you to create a key on a column with NULL values.
A table in MySQL cannot contain more than 16 keys.When to Use Primary and Unique Keys
In general, every table in the database in which you want to uniquely access a row should have a primary
key.
The AUTO_INCREMENT Option
Imagine that you're going to use a customer number, a synthetic key, to uniquely identify each person in your
database. You would write your CREATE TABLE statement as follows:
CREATE TABLE customers (customer_number INT(8) AUTO_INCREMENT PRIMARY KEY NOT
NULL, last_name VARCHAR(30), ...
Notice that you have an AUTO_INCREMENT option specified for customer_number.This is a convenient way of numbering your entries sequentially and ensuring that two identical entries
can never arise.
AUTO_INCREMENT can be applied to any column in the table of type INTEGER. If you put a NULL or 0
value into a field of this type during an INSERT or UPDATE, MySQL will automatically set the new field
value to 1 greater than the largest value for that column currently in the table.
When used with PRIMARY KEY or UNIQUE, MySQL gives you an easy way of ensuring the integrity of
your key.Dropping a Key
If your database spends a lot of its time handling queries with read operations, and at certain times gets
updated with a large number of write operations, there may be a case for using keys that are dropped just
before update time and reinstated afterward.
You would drop the key with the following syntax:
ALTER TABLE customers DROP INDEX lastn_idx;
...
(perform batch write operations)
...
CREATE INDEX lastn_idx ON customers (lastname);You may also want to drop an index to experiment with database performance. When an index is
dropped, performance may get worse or improve.
Dropping an index does not remove any data. Nevertheless, the DROP command should always be
used with care because DROP TABLE can be disastrous.Summary
This chapter has introduced you to the concept of locks. You have seen that in a multithreaded database
environment, locks may be needed to allow one thread to process a multiline query without risk of another
thread interfering with the data before it has finished. You've seen how READ locks prevent other threads
from writing to the locked tables, while WRITE locks prevent other threads from accessing them at all.
You've examined the mechanism by which MySQL queues up lock requests, prioritizing the granting of
locks according to a given policy.You've also seen how performance can be affected by the use of locks, and how they can be used to
overcome MySQL's lack of transactional control.Keys, or indexes, are an even more fundamental concept that play a role not only in MySQL but in
relational databases of all kinds. At their simplest, keys give you a way of looking up data in an efficient
way. You've seen that the concept of primary keys is the cornerstone of relational database design.Keys can exist in a variety of forms: unique or non-unique; single-column or multiple-column. You've
seen how the whole or just part of a column can be indexed.