Unique Keys
By definition, a unique index allows only unique values in the column. When you construct a WHERE clause to
access one specific row, the unique key will take you to one—and only one—matching row.
Unique keys are not only used for performance, but also for ensuring data integrity. MySQL will not
allow the creation of a second row with duplicate key data.If you try to insert duplicate data into a table where a column is unique, MySQL will give you an error.
The same will happen if you try to update an existing row to make a unique column the same as another
existing row.Additionally, if you try to alter a table and add a unique key to a column where data in that column is
already non-unique, it will generate an error.The following would be possible, in the hope of using customers' last names as a unique key:
ALTER TABLE customers ADD UNIQUE lastn_index (lastname);However, it would be impractical.You can create more than one unique key. For example, add a second unique index as follows:
ALTER TABLE customers ADD UNIQUE address_index (address);If you ran the previous commands in that order, your table would look as follows:
mysql> desc customers;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| lastname | varchar(30) | | PRI | | |
| firstname | varchar(30) | | | | |
| address | varchar(100) | | UNI | | |
| state | varchar(30) | | | | |
| country | varchar(30) | | | | |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
Notice how MySQL has called lastname PRI, and address UNI. It has made lastname the primary
key, a special kind of unique key, automatically.
MySQL encourages you to create a primary key, so much so, that if you DROP lastn_index, it will
now make address the primary key.Unique keys are an important concept—primary keys, in particular. These are covered in more detail a
little later in this chapter.Foreign Keys
Foreign keys are not currently supported in MySQL. Some syntax is included for completeness and to
facilitate porting code from other database systems. However, the commands will not work in current
implementations and allowance of all forms of syntax is not yet complete.
Primary Keys
A primary key is similar in principle to a unique key; its data must be unique, but the primary key of a table
has a more privileged status. Only one primary key can exist for each table, and its field values can never be
null.
A primary key is generally used as a structural link in the database, defining relationships between
different tables. Wherever you want to join from one table to another table, you would like to have that