資料完整性

本章說明完整性限制如何強制執行與資料庫相關的業務規則，並防止在表格中輸入無效的資訊。

資料完整性簡介

資料必須保持資料完整性，這符合資料庫管理員或應用程式開發人員所決定的商業規則。

商業規則會指定必須一律為 true 或永遠為 false 的條件與關係。例如，每個公司都會定義自己的薪資、員工號碼、存貨追蹤等等相關政策。

保證資料完整性的技術

設計資料庫應用程式時，開發人員有幾個選項可以保證儲存在資料庫中的資料完整性。

這些選項包括：

另請參閱：

完整性限制的優點

完整性限制條件是一個使用 SQL 建立和刪除的綱要物件。若要強制執行資料完整性，請儘可能使用完整性限制條件或宣告。

與執行資料完整性的替代方法相比，完整性限制和宣告的優點包括：

另請參閱：

完整性限制的類型

Oracle AI Database 可讓您在表格和資料欄層級套用限制條件，以及涉及多個表格的宣告。

指定為資料欄或屬性定義之一部份的限制條件是內嵌規格。指定為表格定義一部分的限制是超出範圍的設定。

索引鍵是包含在特定類型完整性限制條件定義中的資料欄或資料欄集。關鍵碼描述關聯式資料庫之表格與欄間的關係。金鑰中的個別值稱為金鑰值。

下表說明限制的類型。除了必須內嵌的 NOT NULL 之外，每個都可以指定為內嵌或超出範圍。

表格 1 完整性限制型態

限制條件類型 說明 另請參閱
NOT NULL 允許或不允許插入或更新指定資料欄中包含空值的資料列。 「非 NULL 完整性限制條件」
唯一的授權碼 禁止多個資料列在相同的資料欄或資料欄組合中具有相同的值，但允許某些值為空值。 「唯一限制條件」
主索引鍵 結合 NOT NULL 限制條件和唯一的限制條件。它會禁止多個資料列在相同的資料欄或資料欄組合中具有相同的值，並禁止值為空值。 主索引鍵限制條件
外來索引鍵 指定資料欄作為外來索引鍵，並建立外來索引鍵與主要或唯一索引鍵之間的關係，稱為參考索引鍵。 外來索引鍵限制條件
支票 需要資料庫值遵守指定的條件。 「檢查限制條件」
REF 指定 REF 資料欄中值所允許的資料處理類型，以及這些動作如何影響相依值。在物件關聯式資料庫中，稱為 REF 的內建資料類型會封裝指定物件類型之資料列物件的參照。REF 資料欄上的參照完整性限制條件可確保 REF 有資料列物件。 Oracle AI Database Object-Relational Developer's Guide ，瞭解 REF 限制
Assertions 宣告為涉及應用程式資料庫表格之子查詢的布林表示式。宣告具有限制條件的語意，亦即，資料庫的任務是在交易變更資料並確認這些變更時，確保這些布林表示式的真實性。 SQL 宣告以瞭解宣告

另請參閱：

非空值完整性限制條件

NOT NULL 限制條件要求表格的資料欄不包含空值。空值是沒有值。依照預設，表格中的所有資料欄都允許空值。

NOT NULL 限制適用於不可缺少值的資料欄。例如，hr.employees 表格需要 email 資料欄中的值。嘗試插入沒有電子郵件地址的員工列會產生錯誤：

SQL> INSERT INTO hr.employees (employee_id, last_name) values (999, 'Smith');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMAIL")

如果表格未包含任何資料列或您指定預設值，您只能新增含有 NOT NULL 限制條件的資料欄。

另請參閱：

唯一限制條件

唯一索引鍵限制條件需要資料欄或資料欄集中的每個值都是唯一的。表格中沒有任何資料列在單一資料欄 ( 唯一索引鍵 ) 或一組資料欄 ( 複合唯一索引鍵 ) 中具有唯一索引鍵限制條件的重複值。

備註：索引鍵一詞僅指在完整性限制中定義的資料欄。因為資料庫會透過隱含方式在索引鍵資料欄上建立或重複使用索引來強制實行唯一限制條件，所以唯一索引鍵一詞有時會錯誤地當作唯一索引鍵限制條件或唯一索引的同義字使用。

唯一鍵限制適用於任何不允許重複值的資料欄。唯一限制條件與主索引鍵限制條件不同，其目的是唯一識別每個表格資料列，且通常包含除了唯一以外沒有重要性的值。唯一關鍵碼的範例包括：

如範例 4-1 所示，唯一索引鍵限制條件存在於 hr.employees 表格的 email 資料欄。聲明的相關部分如下：

CREATE TABLE employees    ( ...
    , email          VARCHAR2(25)
        CONSTRAINT   emp_email_nn  NOT NULL ...
    , CONSTRAINT     emp_email_uk  UNIQUE (email) ... );

emp_email_uk 限制條件可確保沒有任何兩位員工擁有相同的電子郵件地址，如下列範例所示：

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

除非同時定義了 NOT NULL 限制條件，否則空值一律會滿足唯一的索引鍵限制條件。因此，一般而言，具有唯一索引鍵限制條件和 NOT NULL 限制條件的資料欄都是如此。此組合會強制使用者在唯一關鍵碼中輸入值，並排除新列資料與現有列資料衝突的可能性。

注意：由於多個資料欄之唯一索引鍵限制條件的搜尋機制，因此部分空值複合唯一索引鍵限制條件的非空值資料欄中不能有相同的值。

範例 7-1 唯一限制條件

SQL> SELECT employee_id, last_name, email FROM employees WHERE email = 'PFAY';

EMPLOYEE_ID LAST_NAME                 EMAIL
----------- ------------------------- -------------------------
        202 Fay                       PFAY

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (999,'Fay','PFAY',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMAIL_UK) violated

另請參閱：

主索引鍵限制條件

在主索引鍵限制條件中，受限於限制條件的一或多個資料欄群組中的值可唯一識別資料列。每個表格都可以有一個主索引鍵，這會有效地命名資料列，並確保沒有重複的資料列。

主索引鍵可以是自然索引鍵或替代索引鍵。自然鍵是由表格中現有屬性所組成的有意義識別碼。例如，自然鍵可以是查詢表中的郵遞區號。

相反地，替代索引鍵是系統產生的增量識別碼，可確保資料表內的唯一性。一般而言， sequence 或 UUID 會產生替代索引鍵。

主索引鍵限制條件的 Oracle AI Database 實作可確保下列敘述句正確無誤：

呼叫主鍵的一般情況為員工的數值識別碼。每個員工都必須有唯一的 ID。員工必須由 employees 表格中一個且只有一個資料列加以描述。

唯一限制中的範例指出現有員工的員工 ID 為 202，其中員工 ID 為主索引鍵。下列範例顯示嘗試新增具有相同員工 ID 的員工，以及沒有 ID 的員工：

SQL> INSERT INTO employees (employee_id, last_name, email, hire_date, job_id)
  1  VALUES (202,'Chan','JCHAN',SYSDATE,'ST_CLERK');
.
.
.
ERROR at line 1:
ORA-00001: unique constraint (HR.EMP_EMP_ID_PK) violated

SQL> INSERT INTO employees (last_name) VALUES ('Chan');
.
.
.
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."EMPLOYEES"."EMPLOYEE_ID")

資料庫會強制使用索引的主索引鍵限制條件。通常，為資料欄建立的主索引鍵限制條件會隱含地建立唯一的索引和 NOT NULL 限制條件。請注意此規則的下列例外情況：

依照預設，隱含建立之索引的名稱是主索引鍵限制條件的名稱。您也可以指定索引的使用者定義名稱。您可以在用於建立限制條件的 CREATE TABLE 或 ALTER TABLE 敘述句中包含 ENABLE 子句，以指定索引的儲存體選項。

另請參閱：Oracle AI Database Get Started with Oracle AI Database Development 和 Oracle AI Database Development Guide，瞭解如何將主索引鍵限制新增至表格

外來索引鍵限制條件

每當兩個表格包含一或多個通用資料欄時，Oracle AI Database 便可透過外來索引鍵限制條件強制執行兩個表格之間的關係，也稱為參考完整性限制條件。

外來索引鍵限制條件需要為定義限制條件之資料欄中的每個值，其他指定之其他表格和資料欄中的值必須相符。參照完整性規則的範例為員工只能在現有部門工作。

下表列出與參考完整性限制相關聯的詞彙。

表 2 參考完整性限制條件

期限 定義
外來索引鍵

參照參照參照索引鍵之限制條件定義中所含的一欄或一組資料欄。例如，employees 中的 department_id 資料欄是參照 departments 中 department_id 資料欄的外來索引鍵。

外來索引鍵可以定義為多個資料欄。不過，複合外來索引鍵必須參照具有相同資料欄數目和相同資料類型的複合主索引鍵或唯一索引鍵。

外來索引鍵的值可以與參考的主要索引鍵或唯一索引鍵值相符，或為空值。如果複合外來索引鍵的任何資料欄為空值，則索引鍵的非空值部分不需要符合父項索引鍵的任何對應部分。
參考的索引鍵 外來索引鍵所參考之表格的唯一索引鍵或主索引鍵。例如，departments 中的 department_id 資料欄是 employees 中 department_id 資料欄的參照索引鍵。
相依或子項表格 包含外來索引鍵的表格。此表格取決於參考的唯一或主索引鍵中的值。例如，employees 表格是 departments 的子項。
參照或父項表格 下階表格的外來鍵所參考的表格。這是此表格的參照索引鍵，決定子項表格中是否允許特定插入或更新。例如，departments 表格是 employees 的父項。

圖 7-1 顯示 employees.department_id 資料欄的外來索引鍵。它保證此資料欄中的每個值都必須符合 departments.department_id 資料欄中的一個值。因此，employees.department_id 資料欄中不能有錯誤的部門號碼。

圖 1：參考完整性限制

cncpt056.gif 圖解描述

自我參考完整性限制

自我參考完整性限制條件是參考相同表格中上階索引鍵的外來索引鍵。

在下圖中，自我參考限制可確保 employees.manager_id 資料欄中的每個值都對應至 employees.employee_id 資料欄中的現有值。例如，員工 102 的經理必須存在於 employees 表格中。此限制條件可排除 manager_id 資料欄中錯誤的員工編號。

圖 2：單一表格參照限制條件

cncpt057.gif 圖解描述

空值和外來索引鍵

關聯式模型允許外來索引鍵值與參考的主要索引鍵或唯一索引鍵值相符，或為空值。例如，hr.employees 中的列可能未指定部門 ID。

如果複合外來索引鍵的任何資料欄為空值，則索引鍵的非空值部分不需要符合父項索引鍵的任何對應部分。例如，reservations 表格可能包含 table_id 和 date 資料欄的複合外來索引鍵，但 table_id 為空值。

父項索引鍵修改與外來索引鍵

外來索引鍵與父項索引鍵之間的關係會影響刪除父項索引鍵。例如，若使用者嘗試刪除此部門的記錄，則此部門員工的記錄會發生什麼事？

修改父項索引鍵時，參照完整性限制條件可以指定下列動作，以便對子項表格中的相依資料列執行：

表格 7-3 概述父項表格中不同參照動作所允許的 DML 敘述句，以及子項表格中的外來索引鍵值。

表格 3 更新與刪除不允許的 DML 陳述式

DML 陳述式 依據上階表格核發 依據下階表格核發
INSERT 如果上階鍵值是唯一的，則一律確定 只有在外來索引鍵值存在於父項索引鍵中、部分或全部為空值時才能確定
UPDATE NO ACTION 如果陳述式沒有參考的上階鍵值，則允許在下階表格中保留任何資料列 如果新的外來索引鍵值仍參考參考參考的索引鍵值，則允許
DELETE NO ACTION 如果子項表格中沒有資料列參照父項索引鍵值，則允許 一律確定
DELETE CASCADE 一律確定 一律確定
DELETE SET NULL 一律確定 一律確定

注意：Oracle Database 的 FOREIGN KEY 完整性限制條件不支援其他參照動作，可以使用資料庫觸發程式強制實行。請參閱觸發程式總覽。

另請參閱：Oracle AI Database SQL Language Reference ，瞭解 ON DELETE 子句

索引與外來索引鍵

作為規則，應該編製外來索引鍵的索引。唯一的例外是從未更新或刪除相符的唯一或主索引鍵。

在子項表格中編製外來索引鍵索引的優點如下：

另請參閱：

檢查限制條件

資料欄或資料欄集合上的檢查限制條件需要每個資料列的指定條件為 true 或不明。

如果 DML 導致評估為 false 的限制條件，則會倒回 SQL 敘述句。檢查限制的主要優點是可以強制執行非常特定的完整性規則。例如，您可以使用檢查限制來強制 hr.employees 表格中的下列規則：

下列範例會在 employees 建立最高薪資限制，並示範當陳述式嘗試插入包含超過上限薪資的資料列時，會發生什麼情況：

SQL> ALTER TABLE employees ADD CONSTRAINT max_emp_sal CHECK (salary < 10001);
SQL> INSERT INTO employees (employee_id,last_name,email,hire_date,job_id,salary)
  1  VALUES (999,'Green','BGREEN',SYSDATE,'ST_CLERK',20000);
.
.
.
ERROR at line 1:
ORA-02290: check constraint (HR.MAX_EMP_SAL) violated

單一資料欄可以有多個參照其定義中資料欄的檢查限制條件。例如，salary 資料欄可以有一個限制條件，以防止超過 10000 的值，以及一個可防止小於 500 的值的其他限制條件。

如果資料欄有多個檢查限制，則必須設計這些限制，使其目的不會衝突。無法假設條件的評估順序。資料庫不會驗證檢查條件不是互斥的。

另請參閱：Oracle AI Database SQL Language Reference，瞭解檢查限制的限制

可預先檢查的檢查限制

標示為 PRECHECK 的檢查限制條件可以在資料庫外部檢查。

建立或更改表格時，會將檢查限制條件設為 PRECHECK 狀態。PRECHECK 狀態會補足限制條件的 ENABLE 和 VALIDATE 狀態以及限制條件，您可以讓這三個狀態同時處於作用中狀態。

如果您使用 JSON 表示檢查限制條件，Oracle AI Database 可讓您匯出 JSON 綱要，並根據應用程式從屬端內的 JSON 綱要驗證程式驗證 JSON 資料。當限制條件設為 PRECHECK 狀態時，表示限制條件具有等效的 JSON 綱要，可保留限制條件的語意。用戶端應用程式開發人員可以預先驗證用戶端應用程式內的限制。資料庫會檢查限制條件，如果無法以 JSON 綱要表示限制條件，則會產生錯誤。

相關主題

宣告

宣告為涉及應用程式資料庫表格之子查詢的布林表示式。

宣告具有限制條件的語意，亦即，資料庫的任務是在交易變更資料並確認這些變更時，確保這些布林表示式的真實性。

下列 SQL 陳述式會建立名為 NO_TRAINERS_IN_BOSTON 的宣告，要求波士頓部門不會僱用任何訓練者。最上層 NOT EXISTS 代表布林表示式，要求 EMP 與 DEPT 表格之間結合的結果集保持空白。

CREATE ASSERTION no_trainers_in_boston CHECK
(NOT EXISTS
   (SELECT 'trainer in Boston'
    FROM   emp e, dept d
    WHERE  e.deptno = d.deptno
      AND  e.job = 'TRAINER'
      AND  d.loc = 'BOSTON'));

宣告是綱要層次的物件。這表示，如果布林表示式參照其他綱要的表格，則需要 ASSERTION REFERENCES 物件權限才能存取這些表格。這與跨兩個綱要的外來索引鍵不同，亦即，必須具備 REFERENCES 物件權限，才能順利建立外來索引鍵，以「指向」至不同綱要中表格的主索引鍵。

宣告是一般資料完整性限制條件。它們可用來取代 CHECK、PRIMARY KEY、UNIQUE 和 FOREIGN KEY 限制條件。

例如，假設 EMP 表格上有下列限制條件：

您可以使用下列宣告來實行這些限制條件：

CREATE ASSERTION commission_not_greater_than_salary CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  e.comm > e.sal));
CREATE ASSERTION emp_empno_pk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.empno = e2.empno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_ename_deptno_uk CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e1, emp e2
   WHERE  e1.ename = e2.ename
     AND  e1.deptno = e2.deptno
     AND  e1.rowid != e2.rowid));
CREATE ASSERTION emp_fk_dept CHECK
(NOT EXISTS
  (SELECT ''
   FROM   emp e
   WHERE  NOT EXISTS
     (SELECT ''
      FROM   dept d
      WHERE  d.deptno = e.deptno)));

不過，我們強烈建議您儘可能一律使用限制條件，而且只有在您無法使用限制條件時才使用宣告。

相關主題

完整性限制條件與宣告的狀態

在限制條件或宣告定義中，您可以指定 Oracle AI Database 強制執行限制條件或宣告的方式和時機，藉此決定限制條件或宣告狀態。

檢查已修改與現有資料

資料庫可讓您指定將限制條件或宣告套用至現有資料或未來資料。如果啟用限制條件或宣告，則資料庫會在輸入或更新時檢查新資料。不符合限制或宣告的資料無法進入資料庫。

例如，在 employees.department_id 上啟用 NOT NULL 限制可確保未來每個資料列都有部門 ID。如果停用限制條件，則表格可以包含違反限制條件的資料列。

您可以將限制條件和宣告設為下列其中一個驗證模式：

VALIDATE 和 NOVALIDATE 的行為一律取決於是否啟用或停用限制條件或宣告。下表摘要列出關係。

表格 4 檢查已修改和現有的資料

修改資料 現有資料 摘要
ENABLE VALIDATE 現有和未來的資料必須遵守限制條件或宣告。如果現有資料列違反限制條件或宣告，嘗試將新的限制條件或宣告套用至已填入的表格會導致錯誤。
ENABLE NOVALIDATE 資料庫會檢查限制條件或宣告，但對於所有資料列並不需要為 true。因此，現有的資料列可能會違反限制條件或宣告，但新的或修改的資料列必須符合規則。此模式通常用於包含已驗證完整性之現有資料的資料倉儲。
DISABLE VALIDATE 資料庫會停用限制條件、刪除其索引，以及防止修改限制的資料欄。宣告不能處於此狀態。
DISABLE NOVALIDATE 不會檢查限制條件或宣告，而且不一定是如此。

另請參閱：Oracle AI Database SQL Language Reference ，瞭解限制狀態

當資料庫檢查限制條件和宣告是否有效時

每個限制條件和宣告都處於無法延緩 (預設) 或可延緩的狀態。此狀態決定 Oracle AI Database 檢查限制條件或宣告的有效性。

下圖顯示可延緩限制條件和宣告的選項。

圖 3：可延緩限制條件和宣告的選項

cncpt313.gif 圖解描述

不可延緩的限制條件與宣告

在不可延緩的限制條件中，Oracle AI Database 絕對不會對交易結束時的限制條件或宣告進行有效性檢查。而是資料庫會在每個敘述句的結尾檢查限制條件或宣告。如果違反限制條件或宣告，則敘述句會倒回。

例如，employees.last_name 資料欄有不可延緩的 NOT NULL 限制條件。如果階段作業嘗試插入沒有姓氏的資料列，資料庫就會立即倒回敘述句，因為違反 NOT NULL 限制條件。未插入資料列。

可延緩的限制條件與宣告

可延緩的限制條件或宣告允許交易使用 SET CONSTRAINT 子句來延緩檢查此限制條件或宣告，直到發出 COMMIT 敘述句為止。如果您變更可能違反限制條件或宣告的資料庫，則此設定可有效地讓您停用限制條件或宣告，直到所有變更都完成為止。

您可以設定資料庫檢查可延緩限制條件或宣告時的預設行為。您可以指定下列其中一個屬性：

假設 employees.last_name 上的可延緩 NOT NULL 限制條件設為 INITIALLY DEFERRED。使用者建立含有 100 個 INSERT 敘述句的交易，其中部分具有空值的 last_name。當使用者嘗試確認時，資料庫會倒回所有 100 個敘述句。不過，如果此限制條件設為 INITIALLY IMMEDIATE，資料庫就不會倒回交易。

如果限制條件造成動作，則資料庫會將此動作視為造成該限制條件之敘述句的一部分，不論是延緩或立即。例如，刪除 departments 中的列會導致刪除 employees 中參照已刪除部門列的所有列。在此情況下，從 employees 刪除會被視為針對 departments 執行之 DELETE 敘述句的一部分。

另請參閱：Oracle AI Database SQL Language Reference，瞭解有關限制屬性及其預設值的資訊

限制條件與宣告檢查的範例

下列範例說明 Oracle AI Database 執行限制條件與宣告的檢查時機。

假設如下：

範例：沒有上階索引鍵值時，在外來索引鍵資料欄中插入值

此範例關注將第一個資料列插入 employees 表格。目前沒有資料列，因此如果 manager_id 資料欄中的值無法參照 employee_id 資料欄中的現有值，該如何輸入資料列？

可能的做法有：

在剖析陳述式之前，會將預設值納入為 INSERT 陳述式的一部分。因此，預設資料欄值會受到所有完整性限制條件檢查的限制。

範例：更新所有外來索引鍵與上階索引鍵值

在此範例中，自行參照限制條件會讓 employees 之 manager_id 資料欄中的項目視 employee_id 資料欄的值而定。

公司已售出。由於此銷售的關係，所有員工編號都必須更新為目前值加上 5000，以與新公司的員工編號協調。如下圖所示，部分員工也是經理人員：

圖 4：更新前的員工表格

cncpt058.gif 圖解描述

因為經理人員號碼也是員工號碼，經理人員號碼也必須增加 5000。您可以執行下列 SQL 敘述句來更新值：

UPDATE employees SET employee_id = employee_id + 5000,
  manager_id = manager_id + 5000;

雖然已定義限制條件來驗證每個 manager_id 值是否與 employee_id 值相符，但先前的敘述句仍有效，因為資料庫會在敘述句完成後有效地檢查限制條件。圖 7-5 顯示資料庫在檢查限制條件之前，先執行整個 SQL 敘述句的動作。

圖 5：限制條件檢查

cncpt059.gif 圖解描述

本節中的範例說明 INSERT 和 UPDATE 敘述句期間的限制條件檢查機制，但是資料庫對所有類型的 DML 敘述句使用相同的機制。資料庫會對所有類型的限制使用相同的機制，而不只是自行參照的限制條件。備註：檢視或同義字上的作業受限於基礎表格上定義的完整性限制。

範例：每個經理都必須有相同部門的職員

此範例會建立聲明，以確保每位經理在同一部門都有職員。

此範例要求您先插入職員，再插入經理人員。執行多個資料列插入時，如同限制條件一樣，會在執行每個 INSERT 敘述句之後驗證宣告。您可以將此宣告設為 DEFERRABLE，這樣就可以先插入管理員，然後再插入職員。驗證會發生在 COMMIT，而不是每個 INSERT 陳述式。

CREATE ASSERTION manager_has_clerk check
(NOT EXISTS
  (SELECT 'manager without clerk'
   FROM   employees m
   WHERE  m.job_id = 'ST_MAN'
     AND  NOT EXISTS
            (SELECT 'a clerk in same department'
             FROM   employees c
             WHERE  c.job_id = 'ST_CLERK'
               AND  c.department_id = m.department_id)));