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

来源:https://www.cnblogs.com/vin-c/archive/2022/05/19/16289115.html
-Advertisement-
Play Games

本文介紹 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/

（完）


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 在虛擬機中運行 Linux 的十大優點
    鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 你可以在虛擬機環境里運行任何操作系統，不論是測試還是為了某種需要。 對於 Linux 而言，它在虛擬環境下的性能會優於其他操作系統。即便你可能會猶豫是否在物理機（裸金屬）上安裝 Linux 系統，你仍然可以在虛擬機中安裝一個性能幾乎和物理機一樣好 ...
  • 13-CubeMx+Keil+Proteus模擬STM32 - Flash ROM
    本文例子參考《STM32單片機開發實例——基於Proteus虛擬模擬與HAL/LL庫》 源代碼：https://github.com/LanLinnet/STM33F103R6 項目要求 單片機將由串口收到的1位元組數據存入Flash ROM的指定地址；按下按鈕BTN，單片機將存儲在Flash ROM ...
  • 固執己見的全棧web框架 — Redwood
    為什麼要使用Redwood Redwood是一個全棧web框架，旨在幫助你從副業項目發展到創業。Redwood的特色是一個端到端的開發工作流，它將React、GraphQL、Prisma、TypeScript、Jest和Storybook中最好的部分編織在一起。 RedwoodJS 是集成 、Pri ...
  • Red Hat Enterprise Linux 9 (RHEL 9) 正式版發佈（含 x86_64 和 aarch64 鏡像下載）
    Termius是微軟的一款SSH終端工具，它支持多平臺。而且操作界面十分ha好看且簡潔，今天分享給大家❤️ 軟體下載 關註下方公眾號，回覆termius獲取下載地址 軟體功能介紹 Termius Mac破解版是一款非常好用而且漂亮的SSH客戶端，能快速遠程式控制制伺服器，可以定製自己喜歡的主題，支持FT ...
  • 大數據Hadoop之——基於Hive的記憶體型SQL查詢引擎Impala（Impala環境部署）
    一、概述 Impala 直接針對存儲在 HDFS、HBase或 Amazon Simple Storage Service (S3)中的 Apache Hadoop 數據提供快速的互動式 SQL 查詢。Impala是一個基於Hive、分散式、大規模並行處理(MPP：Massively Paralle ...
  • 達夢-DBLINK資料庫鏈接
    軟硬體環境 軟體 版本 操作系統 Redhat 7 及以上版本，麒麟V10 DM 資料庫 DM 8.0 及以上版本 CPU 架構 x86、ARM、龍芯、飛騰等國內外主流 CPU DM-Oracle 環境準備 在DM伺服器中完成Dblink環境準備工作。 配置Oracle oci客戶端 在Oracle ...
  • 陳胡：Apache SeaTunnel實現 非CDC數據抽取實踐
    導讀： 隨著全球數據量的不斷增長，越來越多的業務需要支撐高併發、高可用、可擴展、以及海量的數據存儲，在這種情況下，適應各種場景的數據存儲技術也不斷的產生和發展。與此同時，各種資料庫之間的同步與轉化的需求也不斷增多，數據集成成為大數據領域的熱門方向，於是SeaTunnel應運而生。SeaTunnel是 ...
  • hive從入門到放棄(六)——常用文件存儲格式
    hive 存儲格式有很多，但常用的一般是 TextFile、ORC、Parquet 格式，在我們單位最多的也是這三種 hive 預設的文件存儲格式是 TextFile。 除 TextFile 外的其他格式的表不能直接從本地文件導入數據，要先導入到 TextFile 格式的表中，再從表中用 inser ...
一周排行
    -Advertisement-
    Play Games
  • Dapr Outbox 執行流程
    Dapr Outbox 是1.12中的功能。 本文只介紹Dapr Outbox 執行流程，Dapr Outbox基本用法請閱讀官方文檔 。本文中appID=order-processor，topic=orders 本文前提知識：熟悉Dapr狀態管理、Dapr發佈訂閱和Outbox 模式。 Outbo ...
  • Advanced .Net Debugging 7：托管堆與垃圾收集
    引言 在前幾章我們深度講解了單元測試和集成測試的基礎知識，這一章我們來講解一下代碼覆蓋率,代碼覆蓋率是單元測試運行的度量值,覆蓋率通常以百分比表示，用於衡量代碼被測試覆蓋的程度，幫助開發人員評估測試用例的質量和代碼的健壯性。常見的覆蓋率包括語句覆蓋率（Line Coverage）、分支覆蓋率（Bra ...
  • C#S7.NET實現西門子PLCDB塊數據採集的完整步驟
    前言 本文介紹瞭如何使用S7.NET庫實現對西門子PLC DB塊數據的讀寫，記錄了使用電腦模擬，模擬PLC，自至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1.Windows環境下鏈路層網路訪問的行業標準工具（WinPcap_4_1_3.exe）下載鏈接：http ...
  • WPF控制項：密碼框綁定MVVM
    從依賴倒置原則（Dependency Inversion Principle, DIP）到控制反轉（Inversion of Control, IoC）再到依賴註入（Dependency Injection, DI）的演進過程，我們可以理解為一種逐步抽象和解耦的設計思想。這種思想在C#等面向對象的編 ...
  • Python中的私有屬性與私有方法
    關於Python中的私有屬性和私有方法 Python對於類的成員沒有嚴格的訪問控制限制，這與其他面相對對象語言有區別。關於私有屬性和私有方法，有如下要點： 1、通常我們約定，兩個下劃線開頭的屬性是私有的（private）。其他為公共的（public）； 2、類內部可以訪問私有屬性（方法）； 3、類外 ...
  • C++ 訪問說明符詳解：封裝數據，控制訪問，提升安全性
    C++ 訪問說明符 訪問說明符是 C++ 中控制類成員（屬性和方法）可訪問性的關鍵字。它們用於封裝類數據並保護其免受意外修改或濫用。 三種訪問說明符： public：允許從類外部的任何地方訪問成員。 private：僅允許在類內部訪問成員。 protected：允許在類內部及其派生類中訪問成員。 示 ...
  • static_cast與dynamic_cast到底是什麼？
    寫這個隨筆說一下C++的static_cast和dynamic_cast用在子類與父類的指針轉換時的一些事宜。首先，【static_cast，dynamic_cast】【父類指針，子類指針】，兩兩一組，共有4種組合：用 static_cast 父類轉子類、用 static_cast 子類轉父類、使用 ...
  • C語言數據結構：雙向鏈表的增刪操作
    /******************************************************************************************************** * * * 設計雙向鏈表的介面 * * * * Copyright (c) 2023-2 ...
  • @ComponentScan註解的實現，Spring掃描包的過程
    相信接觸過spring做開發的小伙伴們一定使用過@ComponentScan註解 @ComponentScan("com.wangm.lifecycle") public class AppConfig { } @ComponentScan指定basePackage，將包下的類按照一定規則註冊成Be ...
  • opensips開啟python支持
    操作系統 ：CentOS 7.6_x64 opensips版本： 2.4.9 python版本：2.7.5 python作為腳本語言，使用起來很方便，查了下opensips的文檔，支持使用python腳本寫邏輯代碼。今天整理下CentOS7環境下opensips2.4.9的python模塊筆記及使用 ...
所有分類