Oracle檢查約束

在本教學中，您將學習如何使用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檢查約束來指定某個列或某組列中的值必須滿足運算式。