如何使用 SQL 約束、索引和觸發器

本文介紹 SQL 所涉及的幾個數據處理特性：約束、索引和觸發器。約束是實施引用完整性的重要部分，索引可改善數據檢索的性能，觸發器可以用來執行運行前後的處理。一、約束 SQL 已經改進過多個版本，成為非常完善和強大的語言。許多強有力的特性給用戶提供了高級的數據處理技術，如約束。關聯表和引用完整性已 ...

一、約束
二、索引
三、觸發器
四、資料庫安全
五、小結

本文介紹 SQL 所涉及的幾個數據處理特性：約束、索引和觸發器。約束是實施引用完整性的重要部分，索引可改善數據檢索的性能，觸發器可以用來執行運行前後的處理。

一、約束

SQL 已經改進過多個版本，成為非常完善和強大的語言。許多強有力的特性給用戶提供了高級的數據處理技術，如約束。

關聯表和引用完整性已經在前面討論過幾次。正如所述，關係資料庫存儲分解為多個表的數據，每個表存儲相應的數據。利用鍵來建立從一個表到另一個表的引用［由此產生了術語引用完整性（referential integrity）］。

正確地進行關係資料庫設計，需要一種方法保證只在表中插入合法數據。

例如，如果 Orders 表存儲訂單信息，OrderItems 表存儲訂單詳細內容，應該保證 OrderItems 中引用的任何訂單 ID 都存在於 Orders 中。

類似地，在 Orders 表中引用的任意顧客必須存在於 Customers 表中。

雖然可以在插入新行時進行檢查（在另一個表上執行 SELECT，以保證所有值合法並存在），但最好不要這樣做，原因如下。

如果在客戶端層面上實施資料庫完整性規則，則每個客戶端都要被迫實施這些規則，一定會有一些客戶端不實施這些規則。
在執行 UPDATE 和 DELETE 操作時，也必須實施這些規則。
執行客戶端檢查是非常耗時的，而 DBMS 執行這些檢查會相對高效。

約束（constraint）

管理如何插入或處理資料庫數據的規則。

DBMS 通過在資料庫表上施加約束來實施引用完整性。

大多數約束是在表定義中定義的，如如何使用 SQL CREATE TABLE 創建新表所述，用 CREATE TABLE 或 ALTER TABLE 語句。

註意：具體 DBMS 的約束

有幾種不同類型的約束，每個 DBMS 都提供自己的支持。

因此，這裡給出的例子在不同的 DBMS 上可能有不同的反應。在進行試驗之前，請參閱具體的 DBMS 文檔。

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
AS
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
END;

提示：約束比觸發器更快

一般來說，約束的處理比觸發器快，因此在可能的時候，應該儘量使用約束。

四、資料庫安全

對於組織來說，沒有什麼比它的數據更重要了，因此應該保護這些數據，使其不被偷盜或任意瀏覽。

當然，數據也必須允許需要訪問它的用戶訪問，因此大多數 DBMS 都給管理員提供了管理機制，利用管理機制授予或限制對數據的訪問。

任何安全系統的基礎都是用戶授權和身份確認。這是一種處理，通過這種處理對用戶進行確認，保證他是有權用戶，允許執行他要執行的操作。

有的 DBMS 為此結合使用了操作系統的安全措施，而有的維護自己的用戶及密碼列表，還有一些結合使用外部目錄服務伺服器。

一般說來，需要保護的操作有：

對資料庫管理功能（創建表、更改或刪除已存在的表等）的訪問；
對特定資料庫或表的訪問；
訪問的類型（只讀、對特定列的訪問等）；
僅通過視圖或存儲過程對錶進行訪問；
創建多層次的安全措施，從而允許多種基於登錄的訪問和控制；
限制管理用戶賬號的能力。

安全性使用 SQL 的 GRANT 和 REVOKE 語句來管理，不過，大多數 DBMS 提供了互動式的管理實用程式，這些實用程式在內部使用 GRANT 和 REVOKE 語句。

五、小結

本文介紹瞭如何使用 SQL 的一些高級特性。約束是實施引用完整性的重要部分，索引可改善數據檢索的性能，觸發器可以用來執行運行前後的處理，安全選項可用來管理數據訪問。

不同的 DBMS 可能會以不同的形式提供這些特性，更詳細的信息請參閱具體的 DBMS 文檔。

原文鏈接：https://www.developerastrid.com/sql/sql-constraints-indexes-triggers/

（完）