Oracle檢查約束 - tw511教學網

在本教學中，您將學習如何使用Oracle檢查約束來強制域(列)的完整性。

Oracle Check約束簡介

Oracle檢查約束允許通過限制一列或多列所接受的值來強制執行域完整性。

要建立一個檢查約束，可以定義一個返回true或false的邏輯表示式。 Oracle使用此表示式來驗證正在插入或更新的資料。如果表示式的計算結果為true，則Oracle接受資料並進行插入或更新。否則，Oracle將拒絕這些資料，新資料根本不會插入或更新。

可以將檢查約束應用於一列或一組列上。一列可能有一個或多個檢查約束。

將多個檢查約束應用於列時，請確保它們不是相互排斥的。另外，不應該假定表示式以任何特定順序的評估。

建立檢查約束語法

通常，在建立表時建立列上的檢查約束，如下語句所示：

CREATE TABLE table_name (
    ...
    column_name data_type CHECK (expression),
    ...
);

在這種語法中，檢查約束由關鍵字CHECK和括號中的表示式組成。該表達應該總是涉及到這個約束列。否則，檢查約束沒有任何意義。

如果要為檢查約束指定一個顯式名稱，則使用下面的CONSTRAINT子句：

CONSTRAINT check_constraint_name
CHECK (expression);

當檢查約束與表列相同的行時，其語法被稱為行內約束。
另外，您可以使用如下的外聯約束語法：

CREATE TABLE table_name (
    ...,
    CONSTRAINT check_constraint_name CHECK (expresssion)
);

建立Oracle Check約束範例

以下範例建立試圖插入0或負面購買價格將導致錯誤：價格為正值的parts表：

CREATE TABLE parts (
    part_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    part_name VARCHAR2(255) NOT NULL,
    buy_price NUMBER(9,2) CHECK(buy_price > 0),
    PRIMARY KEY(part_id)
);

試圖插入0或負面價格將產生錯誤：

INSERT INTO parts(part_name, buy_price)
VALUES('HDD',0);

Oracle發出以下錯誤：

SQL Error: ORA-02290: check constraint (OT.SYS_C0010681) violated

在此錯誤訊息中，SYS_C0010681是Oracle分配的檢查約束的名稱，OT是模式名稱。

為了更好地分析錯誤資訊並在之後參照約束，可以給一個檢查約束一個明確的名稱：

DROP TABLE parts;

CREATE TABLE parts (
    part_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    part_name VARCHAR2(255) NOT NULL,
    buy_price NUMBER(9,2) CONSTRAINT check_positive_buy_price CHECK(buy_price > 0),
    PRIMARY KEY(part_id)
);

現在，如果嘗試插入價格為負數的零件：

INSERT INTO parts(part_name, buy_price)
VALUES('Screen',-100);

錯誤資訊更加精確：

SQL Error: ORA-02290: check constraint (OT.CHECK_POSITIVE_BUY_PRICE) violated

將檢查約束新增到表

要將檢查約束新增到現有表，可以使用ALTER TABLE ADD CONSTRAINT語句，如下所示：

ALTER TABLE table_name
ADD CONSTRAINT check_constraint_name CHECK(expression);

例如，以下語句將成本列新增到parts表中：

ALTER TABLE parts
ADD cost NUMBER(9,2);

假設cost列必須是正的，並且也大於或等於購買價格。要強制執行這些規則，您需要將兩個檢查約束新增到parts表中：

ALTER TABLE parts
ADD CONSTRAINT check_positive_cost CHECK (cost > 0);

ALTER TABLE parts
ADD CONSTRAINT check_valid_cost CHECK (cost >= buy_price);

刪除檢查約束

要刪除檢查約束，請按如下所示使用ALTER TABLE DROP CONSTRAINT語句：

ALTER TABLE table_name
DROP CONSTRAINT check_constraint_name;

例如，要刪除check_valid_cost約束，請使用以下語句：

ALTER TABLE parts
DROP CONSTRAINT check_valid_cost;

禁用/啟用檢查約束

以下語句禁用並啟用檢查約束：

ALTER TABLE table_name
DISABLE CONSTRAINT check_constraint_name;

ALTER TABLE table_name
ENABLE CONSTRAINT check_constraint_name;

例如，要暫時禁用check_positive_buy_price約束，請使用以下語句：

ALTER TABLE table_name
DISABLE CONSTRAINT check_positive_buy_price;

要啟用check_positive_buy_price約束：

ALTER TABLE table_name
ENABLE CONSTRAINT check_positive_buy_price;

Oracle檢查約束的限制

Oracle檢查約束受到以下限制：

可以只為表定義檢查約束，而不是檢視。
檢查約束的表示式可以參照表中的任何列，但不能參照其他表的列。
該表示式也不能包含以下結構之一：
- 非確定性函式，如：SYSDATE，CURRENT_DATE和CURRENT_TIMESTAMP。
  子查詢或標量子查詢表示式。
- 呼叫任何使用者定義的函式。
- 呼叫任何使用者定義的函式。
- 巢狀表列或屬性。
- 偽列CURRVAL，NEXTVAL，LEVEL或ROWNUM。
- 日期常數沒有完全指定。

在本教學中，您已學習如何使用Oracle檢查約束來指定某個列或某組列中的值必須滿足表示式。