Oracle Check Constraint

Summary: in this tutorial, you will learn how to use the Oracle check constraint to enforce domain integrity.

Introduction to Oracle Check constraint

An Oracle check constraint allows you to enforce domain integrity by limiting the values accepted by one or more columns.

To create a check constraint, you define a logical expression that returns true or false. Oracle uses this expression to validate the data that is being inserted or updated. If the expression evaluates to true, Oracle accepts the data and carry the insert or update. Otherwise, Oracle will reject the data and does not insert or update at all.

You can apply a check constraint to a column or a group of columns. A column may have one or more check constraints.

When you apply multiple check constraints to a column, make sure that they are not mutually exclusive. In addition, you should not assume any particular order of evaluation of the expressions.

Creating Check constraint syntax

Typically, you create a check constraint on a column when you create the table:

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

In this syntax, a check constraint consists of the keyword CHECK followed by an expression in parentheses. The expression should always involve the column thus constrained. Otherwise, the check constraint does not make any sense.

If you want to assign the check constraint an explicit name, you use the CONSTRAINT clause below:

CONSTRAINT check_constraint_name
CHECK (expression);  

When a check constraint is on the same line as the table column, its syntax is called in-line constraint.

In addition, you can use the out-of-line constraint syntax as follows:

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

Creating Oracle Check constraint examples

The following example creates the parts table whose buy prices are positive:

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)
);

Attempting to insert 0 or negative buy price will cause an error:

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

Oracle issued the following error:

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

In this error message, SYS_C0010681 is the name of the check constraint assigned by Oracle and OT is the schema name.

To better analyzing the error message and to refer to the constraint later, you can give a check constraint an explicit name:

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)
);

Now, if you try to insert a part with a negative price:

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

The error message is more precise:

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

Add Check constraint to a table

To add a check constraint to an existing table, you use the ALTER TABLE ADD CONSTRAINT statement as follows:

ALTER TABLE table_name
ADD CONSTRAINT check_constraint_name CHECK(expression);

For example, the following statement adds the cost column to the parts table:

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

Suppose the cost must be positive and it is also greater or equal to the buy price. To enforce these rules, you add two check constraints to the parts table:

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

ALTER TABLE parts
ADD CONSTRAINT check_valid_cost CHECK (cost 

Drop checking constraint

To drop a check constraint, you use the ALTER TABLE DROP CONSTRAINT statement as follows:

ALTER TABLE table_name
DROP CONSTRAINT check_constraint_name;

For example, to drop the check_valid_cost constraint, you use the following statement:

ALTER TABLE parts
DROP CONSTRAINT check_valid_cost;

Disable / Enable check constraint

The following statements disable and enable a check constraint:

ALTER TABLE table_name
DISABLE CONSTRAINT check_constraint_name;

ALTER TABLE table_name
ENABLE CONSTRAINT check_constraint_name;

For example, to temporarily disable the check_positive_buy_price constraint, you use the following statement:

ALTER TABLE table_name
DISABLE CONSTRAINT check_positive_buy_price;

And to enable the check_positive_buy_price constraint:

ALTER TABLE table_name
ENABLE CONSTRAINT check_positive_buy_price;

Restrictions of Oracle check constraint

Oracle check constraints are subject to the following restrictions:

1. You can define check constraints for tables only, not views.
2. The expression of the check constraint can refer to any column in the table, but it cannot refer to columns of other tables.
3. The expression also cannot contain one of the following constructs:
   • Non-deterministic functions such as SYSDATE, CURRENT_DATE, and CURRENT_TIMESTAMP.
   • Subqueries or a scalar subquery expressions.
   • Calls to any user-defined functions.
   • Nested table columns or attributes.
   • The pseudo-columns CURRVAL, NEXTVAL, LEVEL, or ROWNUM.
   • Date constants that are not fully specified.

In this tutorial, you have learned how to use Oracle check constraint to specify that the values in a certain column or a group of columns must satisfy an expression.