键和约束

现在我们已经定义了主表，让我们试着思考一下表内数据的外观。表中的每一行都描述了一个对象，可能是一本书，也可能是一个客户。如果我们的应用程序出现错误，允许我们用相同的数据创建书籍或客户，会发生什么情况？数据库将如何区分它们？理论上，我们会为客户分配 ID，以避免这些情况，但我们如何才能确保 ID 不重复呢？

MySQL 有一种机制允许您对数据执行某些限制。除了您已经看到的 NOT NULL 或 UNSIGNED 等属性外，您还可以告诉 MySQL 某些字段比其他字段更特殊，并指示它为这些字段添加一些行为。这些机制称为键，有四种类型：主键、唯一键、外键和索引。让我们仔细看看它们。

主键

主键是标识表中唯一行的字段。同一个表中不能有两个相同的值，也不能为空。在定义对象的表中添加一个主键几乎是必须的，因为它可以确保你始终能够通过这个字段来区分两条记录。

让主键如此吸引人的另一个原因是，它可以将主键设置为自动递增的数值；也就是说，你不必为 ID 赋值，MySQL 就会选择最新插入的 ID 并将其递增 1，就像我们在使用 Unique 特性时所做的那样。当然，要做到这一点，你的字段必须是整数数据类型。事实上，我们强烈建议您始终将主键定义为整数，即使实际对象根本没有这个 ID。原因是你应该通过这个数字 ID 搜索一条记录，因为它是唯一的，而且 MySQL 会通过将字段设置为键来提高性能。

然后，让我们为图书表添加一个 ID。为了添加新字段，我们需要修改表。有一条命令允许你这样做： ALTER TABLE。使用这条命令，你可以修改任何现有字段的定义、添加新字段或删除现有字段。由于我们添加的字段将是主键，并且是自动递增的，因此我们可以在字段定义中添加所有这些修饰符。执行以下代码

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

注意命令末尾的 FIRST。添加新字段时，如果希望它们出现在与表格末尾不同的位置，则需要指定位置。可以是 FIRST 或 AFTER <other field>。为方便起见，表的主键是表中的第一个字段。

由于表 customer 中已经有一个 ID 字段，我们不必再添加它，而是修改它。为此，我们只需使用带有 MODIFY 选项的 ALTER TABLE 命令，指定已存在字段的新定义，如下所示：

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

外键

假设我们需要记录借阅图书的情况。表格应包含借书、借书人和借书时间。那么，您会使用哪种数据来识别图书或客户呢？是用书名还是姓名？那么，我们应该使用某种数据来标识这些表中的唯一行，这种 "数据 "就是主键。有了这个操作，我们就不会再使用可能同时指向两条或多条记录的引用了。

然后，我们可以创建一个包含 book_id 和 customer_id 作为数字字段的表，其中包含引用这两个表的 ID。作为第一种方法，这种方法是合理的，但我们也能发现一些不足之处。例如，如果我们插入了错误的 ID，而这些 ID 并不存在于 book 或 customer 中，会发生什么情况？我们可以在 PHP 端编写一些代码，以确保在从 borrowed_ books 中获取信息时，只显示正确的信息。我们甚至可以设置一个例程，定期检查错误行并将其删除，从而解决错误数据浪费磁盘空间的问题。不过，就像在 MySQL 中使用 Unique 特性和添加主键一样，让数据库系统来管理这些事情通常会更好，因为性能通常会更好，而且你也不需要编写额外的代码。

MySQL 允许你创建强制引用其他表的键。这些键被称为外键，它们是我们被迫使用 InnoDB 表引擎而不是其他表引擎的主要原因。外键定义并执行了该字段与其他表的另一条记录之间的引用。如果为带有外键的字段提供的 ID 不存在于被引用的表中，查询就会失败。此外，如果有一条有效的 borrowed_books 行指向一本现有的书，而你从书表中删除了该条目，MySQL 会对此提出抱怨—尽管你很快就能自定义这种行为，因为这种操作会在系统中留下错误的数据。正如你所注意到的，这比编写代码来管理这些情况要有用得多。

让我们创建包含图书、客户引用和日期的借阅图书表。请注意，我们必须在定义字段后定义外键，而不是在定义主键时定义外键，如下所示：

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)

与 SHOW CREATE SCHEMA 一样，您也可以查看表的外观。与 DESC 命令不同的是，该命令还会显示键的信息。让我们来看看它是如何工作的：

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)

请注意这里有两件重要的事情。一方面，我们有两个没有定义的额外键。原因是在定义外键时，MySQL 也会将字段定义为键，用来提高表的性能；我们稍后会研究这个问题。另一个需要注意的因素是，MySQL 自己定义了键的名称。这是必要的，因为我们需要在更改或删除键时能够引用它们。你可以让 MySQL 为你命名键，也可以在创建键时指定你喜欢的名称。

我们正在经营一家书店，即使我们允许客户借书，我们也希望能够出售图书。销售是我们需要跟踪的一个非常重要的因素，因为顾客可能想要查看这些书籍，或者您可能只需要提供这些信息用于纳税目的。与借书相比，知道图书、客户和日期就足够了，在这里，我们需要为销售设置 ID，以便向客户识别。

然而，这张桌子比其他桌子更难设计，不仅仅是因为 ID 的问题。想想看：顾客是一本一本地买书呢？还是一次性购买任意数量的书籍？因此，我们需要允许表格包含未定义数量的书籍。在 PHP 中，这很容易，我们只需使用数组即可，但 MySQL 中没有数组。解决这个问题有两种方法。

一种解决方案是将销售 ID 设置为普通整数字段，而不是主键。这样，我们就能在销售表中插入多条记录，每借阅一本图书插入一条记录。然而，这个解决方案并不理想，因为我们失去了定义一个非常好的主键的机会，因为它包含了销售 ID。此外，我们还重复了有关客户和日期的数据，因为它们总是相同的。

第二种解决方案，也就是我们将要实施的方案，是创建一个作为 "列表 "的分隔表。我们仍将保留销售表，其中包含作为主键的销售 ID、作为外键的客户 ID 和日期。不过，我们将创建第二个表，命名为 sale_book，并在其中定义销售 ID、图书 ID 和客户购买的同一版本图书的数量。这样，我们就能立即获得有关客户和日期的信息，并能在 sale_book 列表表中插入所需的任意行，而不会重复任何数据。让我们看看如何创建这些数据：

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)

请记住，应始终先创建 sales 表，因为如果先创建带有外键的 sale_book 表，然后再引用一个还不存在的表，MySQL 就会抱怨。

在本节中，我们创建了三个新表，它们相互关联。现在是更新表图的好时机。请注意，当定义了外键时，我们会将字段与表连接起来。看一看：

唯一键

大家都知道，主键非常有用，因为它具有多种功能。其中之一就是字段必须是唯一的。但是，每个表只能定义一个主键，即使你可能有几个字段是唯一的。为了修改这一限制，MySQL 加入了唯一键。它们的作用是确保字段不会在多行中重复，但它们不具备主键的其他功能，如自动递增。此外，唯一键可以为空。

我们的图书表和客户表中就有很好的唯一键候选项。书籍可能有相同的标题，而且同一作者的书肯定不止一本。但是，它们也有唯一的 ISBN；两本不同的书不应该有相同的 ISBN。同样，即使两个客户的名字相同，他们的电子邮件地址也总是不同的。让我们使用 ALTER TABLE 命令添加这两个键，当然也可以在创建表格时添加，就像添加外键一样，如下所示：

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

索引

索引是键的同义词，是不需要像其他键一样有任何特殊行为的字段，但它们在我们的查询中足够重要。因此，我们将要求 MySQL 对它们进行一些处理，以便在通过该字段进行查询时执行得更好。你还记得在添加外键时，MySQL 会向表中添加额外的键吗？那些也是索引。

想想应用程序将如何使用数据库。我们想向客户显示图书目录，但我们不能确定一次显示所有图书。客户希望对结果进行过滤，而最常见的过滤方式之一就是指定他们正在查找的图书的书名。由此我们可以得出，书名将经常被用来筛选图书，因此我们要为这个字段添加一个索引。让我们通过以下代码添加索引：

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

请记住，所有其他键也提供索引。书籍、客户和销售的 ID、国际标准书号和电子邮件都已经建立了索引，因此没有必要在此添加另一个索引。另外，尽量不要为每个字段都添加索引，因为这样做会导致索引过多，从而使某些类型的查询比没有索引时更慢！