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
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
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
ALTER TABLE table_name ENABLE CONSTRAINT check_positive_buy_price;
Restrictions of Oracle check constraint
Oracle check constraints are subject to the following restrictions:
- You can define check constraints for tables only, not views.
- The expression of the check constraint can refer to any column in the table, but it cannot refer to columns of other tables.
- The expression also cannot contain one of the following constructs:
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.