如何使用 SQL 约束、索引和触发器

link之家

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

1.1 主键

我们在学习 SQL 之前需要了解的基础知识简单提过主键。

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。

换句话说，表中的一列（或多个列）的值唯一标识表中的每一行。这方便了直接或交互地处理表中的行。

没有主键，要安全地 UPDATE 或 DELETE 特定行而不影响其他行会非常困难。

表中任意列只要满足以下条件，都可以用于主键。

任意两行的主键值都不相同。

每行都具有一个主键值（即列中不允许


    NULL

值）。

包含主键值的列从不修改或更新。（大多数 DBMS 不允许这么做，但如果你使用的 DBMS 允许这样做，好吧，千万别！）

主键值不能重用。如果从表中删除某一行，其主键值不分配给新行。

一种定义主键的方法是创建它，如下所示。

CREATE TABLE Vendors
    vend_id         CHAR(10)       NOT NULL PRIMARY KEY,
    vend_name       CHAR(50)       NOT NULL,
    vend_address    CHAR(50)       NULL,
    vend_city       CHAR(50)       NULL,
    vend_state      CHAR(5)        NULL,
    vend_zip        CHAR(10)       NULL,
    vend_country    CHAR(50)       NULL
在此例子中，给表的 vend_id 列定义添加关键字 PRIMARY KEY，使其成为主键。
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);
这里定义相同的列为主键，但使用的是 CONSTRAINT 语法。此语法也可以用于 CREATE TABLE 和 ALTER TABLE 语句。
说明：SQLite 中的键
SQLite 不允许使用 ALTER TABLE 定义键，要求在初始的 CREATE TABLE 语句中定义它们。
1.2 外键
外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。我们举个例子来理解外键。
Orders 表将录入到系统的每个订单作为一行包含其中。
顾客信息存储在 Customers 表中。Orders 表中的订单通过顾客 ID 与 Customers 表中的特定行相关联。
顾客 ID 为 Customers 表的主键，每个顾客都有唯一的 ID。订单号为 Orders 表的主键，每个订单都有唯一的订单号。
Orders 表中顾客 ID 列的值不一定是唯一的。
如果某个顾客有多个订单，则有多个行具有相同的顾客 ID（虽然每个订单都有不同的订单号）。同时，Orders 表中顾客 ID 列的合法值为 Customers 表中顾客的 ID。
这就是外键的作用。在这个例子中，在 Orders 的顾客 ID 列上定义了一个外键，因此该列只能接受 Customers 表的主键值。
下面是定义这个外键的方法。
CREATE TABLE Orders
    order_num    INTEGER    NOT NULL PRIMARY KEY,
    order_date   DATETIME   NOT NULL,
    cust_id      CHAR(10)   NOT NULL REFERENCES Customers(cust_id)
其中的表定义使用了 REFERENCES 关键字，它表示 cust_id 中的任何值都必须是 Customers 表的 cust_id 中的值。
相同的工作也可以在 ALTER TABLE 语句中用 CONSTRAINT 语法来完成：
ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
提示：外键有助防止意外删除
如 如何使用 SQL UPDATE 和 DELETE 语句更新或删除表数据 所述，除帮助保证引用完整性外，外键还有另一个重要作用。
在定义外键后，DBMS 不允许删除在另一个表中具有关联行的行。
例如，不能删除关联订单的顾客。删除该顾客的唯一方法是首先删除相关的订单（这表示还要删除相关的订单项）。由于需要一系列的删除，因而利用外键可以防止意外删除数据。
有的 DBMS 支持称为级联删除（cascading delete）的特性。如果启用，该特性在从一个表中删除行时删除所有相关的数据。
例如，如果启用级联删除并且从 Customers 表中删除某个顾客，则任何关联的订单行也会被自动删除。
1.3 唯一约束
唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。
表可包含多个唯一约束，但每个表只允许一个主键。
唯一约束列可包含 NULL 值。
唯一约束列可修改或更新。
唯一约束列的值可重复使用。
与主键不一样，唯一约束不能用来定义外键。
employees 表是一个使用约束的例子。每个雇员都有唯一的社会安全号，但我们并不想用它作主键，因为它太长（而且我们也不想使该信息容易利用）。
因此，每个雇员除了其社会安全号外还有唯一的雇员 ID（主键）。
雇员 ID 是主键，可以确定它是唯一的。你可能还想使 DBMS 保证每个社会安全号也是唯一的（保证输入错误不会导致使用他人号码）。可以通过在社会安全号列上定义 UNIQUE 约束做到。
唯一约束的语法类似于其他约束的语法。唯一约束既可以用 UNIQUE 关键字在表定义中定义，也可以用单独的 CONSTRAINT 定义。
1.4 检查约束
检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。检查约束的常见用途有以下几点。
检查最小或最大值。例如，防止 0 个物品的订单（即使 0 是合法的数）。
指定范围。例如，保证发货日期大于等于今天的日期，但不超过今天起一年后的日期。
只允许特定的值。例如，在性别字段中只允许 M 或 F。
换句话说，学习 SQL 之前需要了解的基础知识 介绍的数据类型限制了列中可保存的数据的类型。
检查约束在数据类型内又做了进一步的限制，这些限制极其重要，可以确保插入数据库的数据正是你想要的数据。
不需要依赖于客户端应用程序或用户来保证正确获取它，DBMS 本身将会拒绝任何无效的数据。
下面的例子对 OrderItems 表施加了检查约束，它保证所有物品的数量大于 0。
CREATE TABLE OrderItems
    order_num     INTEGER     NOT NULL,
    order_item    INTEGER     NOT NULL,
    prod_id       CHAR(10)    NOT NULL,
    quantity      INTEGER     NOT NULL CHECK (quantity > 0),
    item_price    MONEY       NOT NULL
利用这个约束，任何插入（或更新）的行都会被检查，保证 quantity 大于 0。
检查名为 gender 的列只包含 M 或 F，可编写如下的 ALTER TABLE 语句：
ADD CONSTRAINT CHECK (gender LIKE '[MF]');
提示：用户定义数据类型
有的 DBMS 允许用户定义自己的数据类型。
它们是定义检查约束（或其他约束）的基本简单数据类型。
例如，你可以定义自己的名为 gender 的数据类型，它是单字符的文本数据类型，带限制其值为 M 或 F（对于未知值或许还允许 NULL）的检查约束。
然后，可以将此数据类型用于表的定义。定制数据类型的优点是只需施加约束一次（在数据类型定义中），而每当使用该数据类型时，都会自动应用这些约束。
请查阅相应的 DBMS 文档，看它是否支持自定义数据类型。
索引用来排序数据以加快搜索和排序操作的速度。想象一本书后的索引，可以帮助你理解数据库的索引。
假如要找出书中所有的“数据类型”这个词，简单的办法是从第 1 页开始，浏览每一行。虽然这样做可以完成任务，但显然不是一种好的办法。
浏览少数几页文字可能还行，但以这种方式浏览整部书就不可行了。随着要搜索的页数不断增加，找出所需词汇的时间也会增加。
这就是书籍要有索引的原因。索引按字母顺序列出词汇及其在书中的位置。为了搜索“数据类型”一词，可在索引中找出该词，确定它出现在哪些页中。然后再翻到这些页，找出“数据类型”一词。
索引靠什么起作用？很简单，就是恰当的排序。找出书中词汇的困难不在于必须进行多少搜索，而在于书的内容没有按词汇排序。如果书的内容像字典一样排序，则索引没有必要（因此字典就没有索引）。
数据库索引的作用也一样。主键数据总是排序的，这是 DBMS 的工作。因此，按主键检索特定行总是一种快速有效的操作。
但是，搜索其他列中的值通常效率不高。
例如，如果想搜索住在某个州的客户，怎么办？因为表数据并未按州排序，DBMS 必须读出表中所有行（从第一行开始），看其是否匹配。这就像要从没有索引的书中找出词汇一样。
解决方法是使用索引。可以在一个或多个列上定义索引，使 DBMS 保存其内容的一个排过序的列表。
在定义了索引后，DBMS 以使用书的索引类似的方法使用它。DBMS 搜索排过序的索引，找出匹配的位置，然后检索这些行。
在开始创建索引前，应该记住以下内容。
索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS 必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列（例如，州加上城市）。这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。
没有严格的规则要求什么应该索引，何时索引。大多数 DBMS 提供了可用来确定索引效率的实用程序，应该经常使用这些实用程序。
索引用 CREATE INDEX 语句创建（不同 DBMS 创建索引的语句变化很大）。下面的语句在 Products 表的产品名列上创建一个简单的索引。
CREATE INDEX prod_name_ind
ON Products (prod_name);
索引必须唯一命名。这里的索引名 prod_name_ind 在关键字 CREATE INDEX 之后定义。ON 用来指定被索引的表，而索引中包含的列（此例中仅有一列）在表名后的圆括号中给出。
提示：检查索引
索引的效率随表数据的增加或改变而变化。许多数据库管理员发现，过去创建的某个理想的索引经过几个月的数据处理后可能变得不再理想了。
最好定期检查索引，并根据需要对索引进行调整。
三、触发器
触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的 INSERT、UPDATE 和 DELETE 操作（或组合）相关联。
与存储过程不一样（存储过程只是简单的存储 SQL 语句），触发器与单个的表相关联。
与 Orders 表上的 INSERT 操作相关联的触发器只在 Orders 表中插入行时执行。
类似地，Customers 表上的 INSERT 和 UPDATE 操作的触发器只在表上出现这些操作时执行。
触发器内的代码具有以下数据的访问权：
INSERT 操作中的所有新数据；
UPDATE 操作中的所有新数据和旧数据；
DELETE 操作中删除的数据。
根据所使用的 DBMS 的不同，触发器可在特定操作执行之前或之后执行。
下面是触发器的一些常见用途。
保证数据一致。例如，在 INSERT 或 UPDATE 操作中将所有州名转换为大写。
基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。
计算计算列的值或更新时间戳。
读者可能已经注意到了，不同 DBMS 的触发器创建语法差异很大，更详细的信息请参阅相应的文档。
下面的例子创建一个触发器，它对所有 INSERT 和 UPDATE 操作，将 Customers 表中的 cust_state 列转换为大写。
这是本例子的 SQL Server 版本。
CREATE TRIGGER customer_state
ON Customers
FOR INSERT, UPDATE
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = inserted.cust_id;
这是本例子的 Oracle 和 PostgreSQL 的版本：
CREATE TRIGGER customer_state
AFTER INSERT OR UPDATE
FOR EACH ROW
BEGIN
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Customers.cust_id = :OLD.cust_id
提示：约束比触发器更快
一般来说，约束的处理比触发器快，因此在可能的时候，应该尽量使用约束。
四、数据库安全
对于组织来说，没有什么比它的数据更重要了，因此应该保护这些数据，使其不被偷盗或任意浏览。
当然，数据也必须允许需要访问它的用户访问，因此大多数 DBMS 都给管理员提供了管理机制，利用管理机制授予或限制对数据的访问。
任何安全系统的基础都是用户授权和身份确认。这是一种处理，通过这种处理对用户进行确认，保证他是有权用户，允许执行他要执行的操作。
有的 DBMS 为此结合使用了操作系统的安全措施，而有的维护自己的用户及密码列表，还有一些结合使用外部目录服务服务器。
一般说来，需要保护的操作有：
对数据库管理功能（创建表、更改或删除已存在的表等）的访问；
对特定数据库或表的访问；
访问的类型（只读、对特定列的访问等）；
仅通过视图或存储过程对表进行访问；
创建多层次的安全措施，从而允许多种基于登录的访问和控制；
限制管理用户账号的能力。
安全性使用 SQL 的 GRANT 和 REVOKE 语句来管理，不过，大多数 DBMS 提供了交互式的管理实用程序，这些实用程序在内部使用 GRANT 和 REVOKE 语句。
本文介绍了如何使用 SQL 的一些高级特性。约束是实施引用完整性的重要部分，索引可改善数据检索的性能，触发器可以用来执行运行前后的处理，安全选项可用来管理数据访问。
不同的 DBMS 可能会以不同的形式提供这些特性，更详细的信息请参阅具体的 DBMS 文档。
原文链接：https://www.developerastrid.com/sql/sql-constraints-indexes-triggers/