CHECK Constraint

Ø  It defines a condition that each row must satisfy.
Ø  To satisfy the constraint, each row in the table must make the condition either TRUE or UNKNOWN.
Ø  Oracle does not verify that CHECK CONDITIONS are mutually exclusive.
Restrictions:
Ø  The condition of a CHECK constraint can refer to any column in the same table, but it cannot refer to columns of other tables.
Ø  A single column can have multiple CHECK constraints that can reference the column in the definition.
Ø  There is no limit to the number of CHECK constraints that can be defined on a column.
Ø  The CHECK constraints can be defined at the column level or table level.
Default Option:
Ø  The default option is given to maintain a default value in a column.
Ø  The Option Prevents NULL values from entering the columns, if a row is inserted without a value for a column.
Ø  The default value can be a literal, an expression or a sql functions.
Ø  The default expression must match the data type of the column.
Example:
·         Create Table Dept
                        (
                                    Deptno Number(2)
                                    Contraint CHK_Deptno
                                    Check(Deptno BETWEEN 10 and 90),
                                    Dname varchar2(15) Constraint chk_Dname_up
                                    Check(Dname = upper(Dname) ) Disable,
                                    Loc  varchar2(15)
                                    Contraint chk_loc check(Loc  IN ( ‘ DALLAS’, ‘BOSTON’,’NEW                                                            YORK’,’CHICAGO’))
                        );

·         Create Table Emp
                        (
                                    Empno Number(4)
                                    Constraint PR_Empno Primary key,
                                    Ename varchar2(25) NOT NULL
                                    Constraint Chk_Ename
                                    Check(Ename = UPPER(Ename)),
                                    Job varchar2(30)
                                    Constraint chk_job
                                    Check(Job = UPPER(Job)),
                                    MGR Nubmer(4),
                                    Hiredate Date
                                    DEFAULT SYSDATE,
                                    Sal Number(7,2)
                                    Constraint Sal_nn NOT NULL,
                                    Constraint Chk_sal
                                    Check(Sal Between 2000 and 100000)),
                                    Comm Number(7,2),
                                    Deptno Number(2),
                                    Constraint tot_sal_chk check(sal + comm <= 100000 )

                        );