1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Check constraint

Discussion in 'SQL PL/SQL' started by SBH, Nov 15, 2010.

  1. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    1. Overview

    Entity and referential integrity enforce the rules upon the attributes i.e. they re-structure the attribute properties. But Domain integrity restricts the scope of value that an attribute can accept. The article explains the use of Check constraint in Oracle

    2. Introduction

    Check constraint enforces domain integrity rule on the attributes. The rule must be respected (TRUE) at any instant of an action on the attribute. It specifies a condition using a column, thereby its value which must be satisfied by all the rows in the table.

    3. Syntax

    Column level:
    Code (Text):
    COLUMN [data type] CONSTRAINT [name] [CHECK (condition)]
    Table level:
    Code (Text):
    CONSTRAINT [name] CHECK (condition)
    4. Example

    4.1. Column Level declaration demonstration

    Code (SQL):
    CREATE TABLE TEST
     ( ...,
       GRADE CHAR (1) CONSTRAINT TEST_CHK
              CHECK (UPPER (GRADE) IN (‘A’,’B’,’C’)),
       ...
     );
    4.2. Table Level declaration demonstration

    Code (SQL):
    CREATE TABLE TEST
     ( ...,

       CONSTRAINT TEST_CHK
            CHECK (stdate <= enddate),
     );
    5. Notes

    1. Check constraint can be specified at Column level (Inline specification) and Table level (Out of line specification). Column level can make use of single column while Table level specification allows making use of multiple columns.

    2. Multiple check constraints can be specified over a column. Oracle does not follow any order of evaluation of these conditions but provided they must not conflict with each other (i.e. mutually exclusive).

    3. It can refer any column of the same table, but not from other tables.

    4. It can contain single row functions, range values, IN, BETWEEN operators.

    6. Restrictions

    1. Check constraint cannot be enforced on a view. But views created with ‘WITH CHECK OPTION’ can make use of check constraints.

    2. Following constructs cannot be used while specifying the Check constraint conditions.
    • Subqueries expressions
    • Pseudo columns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
    • User defined and Deterministic functions like CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE,SYSDATE, SYSTIMESTAMP, UID, USER, and USERENV
    • Any reference to a function
    • Nested table columns or attributes