Oracle检查约束

精华

小牛编辑

108浏览

2023-03-14

在本教程中，您将学习如何使用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检查约束来指定某个列或某组列中的值必须满足表达式。