Check Constraints In SQL

 

Check Constraint::

  ->to check values before accepting into a column with user defined condition

at column level::

================

>create table test1(eid int check(eid<5),ename varchar2(10));

EX::

SQL> create table test1(eid int check(eid<5),ename varchar2(10));

Table created.

SQL> insert into test1 values(1,'dan');

1 row created.

SQL> insert into test1 values(2,'sam');

1 row created.

SQL> select * from test1;

       EID ENAME

---------- ----------

         1 dan

         2 sam

SQL> insert into test1 values(5,'sam');

insert into test1 values(5,'sam')

*

ERROR at line 1:

ORA-02290: check constraint (SYSTEM.SYS_C007650) violated

SQL> insert into test1 values(4,'sam');

1 row created.

SQL> select * from test1;

       EID ENAME

---------- ----------

         1 dan

         2 sam

         4 sam

SQL> insert into test1 values(55,'sam');

insert into test1 values(55,'sam')

*

ERROR at line 1:

ORA-02290: check constraint (SYSTEM.SYS_C007650) violated

at table level::

================

>create table test1(eid int,ename varchar2(10),check(eid<5 and ename = higer(ename));

EX::

===

SQL> create table test2(eid int,ename varchar2(10),check(eid<5 and ename=upper(ename)));

Table created.

SQL> insert into test2(1,'DAN');

insert into test2(1,'DAN')

                  *

ERROR at line 1:

ORA-00928: missing SELECT keyword

SQL> insert into test2 values(1,'DAN');

1 row created.

SQL> insert into test2 values(2,'SAM');

1 row created.

SQL> select * from test2;

       EID ENAME

---------- ----------

         1 DAN

         2 SAM

SQL> insert into test2 values(2,'raja');

insert into test2 values(2,'raja')

*

ERROR at line 1:

ORA-02290: check constraint (SYSTEM.SYS_C007651) violated

SQL> insert into test2 values(2,'RAJA');

1 row created.

SQL> select * from test2;

       EID ENAME

---------- ----------

         1 DAN

         2 SAM

         2 RAJA

SQL> insert into test2 values(5,'SAM');

insert into test2 values(5,'SAM')

*

ERROR at line 1:

ORA-02290: check constraint (SYSTEM.SYS_C007651) violated

SQL> insert into test2 values(5,'raja');

insert into test2 values(5,'raja')

*

ERROR at line 1:

ORA-02290: check constraint (SYSTEM.SYS_C007651) violated