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