Oracle檢查約束


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

Oracle Check約束簡介

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

要建立一個檢查約束,可以定義一個返回truefalse的邏輯表示式。 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檢查約束受到以下限制:

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

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