Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / Oracle Basics / Oracle Check Constraint

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.

  • Was this tutorial helpful?
  • YesNo
Previous Oracle Foreign Key
Next Oracle Unique Constraint

Getting Started

  • What Is Oracle Database
  • Install Oracle Database Server
  • Download Oracle Sample Database
  • Create Oracle Sample Database
  • Connect To Oracle Database Server

Oracle Data Manipulation

  • SELECT
  • Oracle DUAL Table
  • ORDER BY
  • SELECT DISTINCT
  • WHERE
  • Table & Column Aliases
  • AND
  • OR
  • FETCH
  • BETWEEN
  • IN
  • LIKE
  • IS NULL
  • Joins
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • CROSS JOIN
  • Self Join
  • GROUP BY
  • HAVING
  • UNION
  • INTERSECT
  • MINUS
  • GROUPING SETS
  • CUBE
  • ROLLUP
  • PIVOT
  • UNPIVOT
  • INSERT
  • INSERT INTO SELECT
  • INSERT ALL
  • UPDATE
  • DELETE
  • MERGE
  • Subquery
  • Correlated Subquery
  • EXISTS
  • NOT EXISTS
  • ANY
  • ALL

Oracle Data Types

  • Oracle Data Types
  • NUMBER
  • FLOAT
  • BINARY_FLOAT
  • CHAR
  • NCHAR
  • VARCHAR2
  • NVARCHAR2
  • DATE
  • INTERVAL
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE

Oracle Data Definition

  • CREATE TABLE
  • Identity Column
  • ALTER TABLE
  • ALTER TABLE ADD Column
  • ALTER TABLE MODIFY Column
  • Drop Columns
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME Table
  • Oracle Virtual Column

Oracle Constraints

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL

Oracle Views

  • CREATE VIEW
  • DROP VIEW
  • Updatable Views
  • Inline Views
  • WITH CHECK OPTION

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.