Oracle Check Constraint

In the Oracle database, the CHECK constraint is used to define logical conditions based on client business rules.

Similar to another constraint, the CHECK constraint also can be created on both column-level and table level. The column level CHECK constraint will be applied only on a particular column, and the table-level CHECK constraint can be applied to multiple columns.

Oracle Check Constraint on Column level

In this method, we are defining constraints on individual columns. It means whenever we are creating a column then immediately we are specifying constraints type. Syntax:-

CREATE TABLE tablename (
col1 datatype(size)
CHECK (logical conditions),
….…
);

Example:-

SQL> CREATE TABLE test (
       name VARCHAR2(10) 
       CHECK(name = UPPER(name))
     );
Table created.

In this table, the column “name” only accept uppercase character.

SQL> INSERT INTO test VALUES('abc');
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0014192) violated

SQL> INSERT INTO test VALUES('AbC');
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0014192) violated

It also don’t allow to insert or update mixup of uppercase and lowercase.

SQL> INSERT INTO test VALUES('ABC');
SQL> INSERT INTO test VALUES('XYZ');
SQL> SELECT * FROM test;
NAME
----------
ABC
XYZ

Another example,

SQL> CREATE TABLE test1 (
       sal NUMBER(10)
       CHECK(sal > 5000)
     );
Table created.

The sal column only accept salary greater than 5000.

SQL> INSERT INTO test1 VALUES(2000);
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0014193) violated

SQL> INSERT INTO test1 VALUES(9000);
1 row created.

SQL> SELECT * FROM test1;
       SAL
----------
      9000

Oracle Check Constraint on Table level

In this method, we are defining constraints on a group of columns. Here first we are defining all columns and after creating the table we are specifying CHECK constraint along with a group of columns.

Syntax:-
CREATE TABLE tablename (
col1 datatype(size),
col2 datatype(size),
…..,
CHECK(condition1 and condition2)
);

Example:-

SQL> CREATE TABLE test2 (
       name VARCHAR2(10),
       sal NUMBER(10),
       CHECK( name = UPPER(name) AND sal > 5000 )
     );
Table created.

SQL> INSERT INTO test2 VALUES('abc', 2000);
ORA-02290: check constraint (SCOTT.SYS_C0014194) violated

SQL> INSERT INTO test2 VALUES('ABC', 2000);
ORA-02290: check constraint (SCOTT.SYS_C0014194) violated

SQL> INSERT INTO test2 VALUES('abc', 9000);
ORA-02290: check constraint (SCOTT.SYS_C0014194) violated

Test2 table accepts only uppercase names and salary greater than 5000. If any of the conditions is violated then the oracle server gives error:- ORA-02290: check constraint violated.

SQL> INSERT INTO test2 VALUES('ABC', 9000);
1 row created.

SQL> SELECT * FROM test2;
NAME              SAL
---------- ----------
ABC              9000

Add CHECK Constraint in Oracle

In all relational databases whenever we are copying a table from another table then except NOT NULL constraint all other constraints are never copied. So, we need to add constraints on the existing table or column.

We can enable CHECK constraint on an existing columns or add a new column.

Enable CHECK constraint on an Existing Column in Oracle

In oracle if we want to enable constraints on existing table existing column then we must use table level syntax method.

Syntax:-
ALTER TABLE tableName
ADD CHECK(conditon1, condition2, …);

Condition:- The columns must not contain any value which violated the CHECK condition, else we will get an error, ORA-02293: cannot validate – check constraint violated

Example:-

SQL> CREATE TABLE test3
     AS
     SELECT * FROM test2;
Table created.

SQL> SELECT * FROM test3;
NAME              SAL
---------- ----------
ABC              9000

Since, table is copied from another table so constraints are not copied.

SQL> INSERT INTO test3 VALUES('xyz', 3000);
SQL> INSERT INTO test3 VALUES('xyz', 9000);
SQL> INSERT INTO test3 VALUES('PQR', 8000);
SQL> SELECT * FROM test3;
NAME              SAL
---------- ----------
ABC              9000
xyz              3000
xyz              9000
PQR              8000

To apply CHECK constraint on an existing column, the column must not have any value which violates the condition. For example:- if we want to apply CHECK constraint name = lower(name) and sal > 5000, then the name column must contain only lowercase values and the sal column must contain a value greater than 5000.

SQL> ALTER TABLE test3 
     ADD CHECK(name = lower(name) 
     AND sal > 5000);
ORA-02293: cannot validate (SCOTT.SYS_C0014195)
- check constraint violated

SQL> DELETE FROM test3 
     WHERE name != lower(name) 
     OR sal < 5000;
3 rows deleted.

SQL> SELECT * FROM test3;
NAME              SAL
---------- ----------
xyz              9000

Now, we can apply the CHECK constraint with the given conditions.

SQL> ALTER TABLE test3 
     ADD CHECK(name = lower(name) 
     AND sal > 5000);
Table altered.

SQL> INSERT INTO test3 VALUES('xyz', 3000);
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0014196) violated

Enable CHECK constraint Without Validating Existing Value

In the previous example, when have seen that whenever the table contains values that violate the CHECK constraint condition then we were not able to enable the CHECK constraint.

But sometime we need to enable CHECK constraint only for new insert or update values, not for existing values. In this case we can use NOVALIDATE.

When we use NOVALIDATE along with CHECK constraint then existing values are not validated , only new value will be checked.

Syntax:-
ALTER TABLE tableName
ADD CHECK(conditon1, condition2, …) NOVALIDATE;

Example:-

SQL> CREATE TABLE test03
     AS
     SELECT * FROM test2;
Table created.

SQL> INSERT INTO test03 VALUES('ABC', 9000);
SQL> INSERT INTO test03 VALUES('xyz', 3000);
SQL> INSERT INTO test03 VALUES('xyz', 9000);
SQL> INSERT INTO test03 VALUES('PQR', 8000);
SQL> SELECT * FROM test03;
NAME              SAL
---------- ----------
ABC              9000
xyz              3000
xyz              9000
PQR              8000

SQL> ALTER TABLE test03
     ADD CHECK(name = lower(name)
     AND sal > 5000) NOVALIDATE;
Table altered.

SQL> INSERT INTO test03 VALUES('UVW', 1000);
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0014215) violated

Add New Column With CHECK Constraint

In all relational databases If you want to add a new column along with constraint then we are using the column-level syntax method. An alternate way to add a constraint on the new column, first create the column and then add a constraint similar to the previous example.

Syntax to create a new column with CHECK constraint,
ALTER TABLE tableName ADD
col1 datatype(size)
CHECK (logical conditions),
………;

Example:-

SQL> CREATE TABLE test4
     AS
     SELECT * FROM test2;
Table created.

SQL> SELECT * FROM test4;
NAME              SAL
---------- ----------
ABC              9000

SQL> ALTER TABLE test4 ADD
     average FLOAT 
     CHECK(average > 40);
Table altered.

SQL> INSERT INTO test4 VALUES('sophia', 2000, 50);
1 row created.

SQL> SELECT * FROM test4;
NAME              SAL    AVERAGE
---------- ---------- ----------
ABC              9000
sophia           2000         50

SQL> INSERT INTO test4 VALUES('alex', 3000, 35);
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0014197) violated

Drop CHECK Constraint in Oracle

We can remove constraints by using its name. The syntax to drop or remove the constraint from the table is,
ALTER TABLE tableName
DROP CONSTRAINTS constraint_name;

Using Pre-defined Constraint Name

In the Oracle database, whenever we are creating a constraint then oracle server internally automatically generates a unique identification number for identifying a constraint uniquely in the format of SYS_Cn, this is also called a pre-defined constraint name. Example:-

SQL> CREATE TABLE test5 (
       sno NUMBER(10)
       CHECK(sno>=100 AND sno<=200)
     );
Table created.

SQL> INSERT INTO test5 VALUES(10);
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C0014198) violated

Here SYS_C0014198 is the Oracle generated unique identification number for the CHECK constraint applied on sno column of the TEST5 table. We can get this information from user_cons_columns.

SQL> DESC user_cons_columns;
 Name              Null?    Type
 ----------------- -------- --------------
 OWNER             NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME   NOT NULL VARCHAR2(30)
 TABLE_NAME        NOT NULL VARCHAR2(30)
 COLUMN_NAME                VARCHAR2(4000)
 POSITION                   NUMBER

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST5';

COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             SYS_C0014198

Note that in the above query table name TEST5 is given in the capital letter. Oracle table name, column names are not case sensitive but records of a table are case sensitive, and here TEST5 is a record of user_cons_columns.

To drop the constraint we can use these constraint names,

SQL> ALTER TABLE TEST5 
     DROP CONSTRAINTS SYS_C0014198;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST5';
no rows selected

When Constraint is enabled at table level then the pre-defined constraint name will be same of those columns.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST2';

COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNAME             SYS_C0014194
SAL               SYS_C0014194

SQL> ALTER TABLE TEST2 
     DROP CONSTRAINTS SYS_C0014194;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST2';
no rows selected.

Assigning User-defined Name To Constraint

While dropping the constraints, the pre-defined constraint name can create confusion for others, and just by seeing the pre-defined constraint name, we can’t identify which constraint is enabled on this column.

In place of a pre-defined constraint name, we can also assign our own name by using the CONSTRAINT keyword by using the following syntax. This is called a user-defined constraint name. Syntax:- CONSTRAINT user-definedname constriantType

Example:-

SQL> CREATE TABLE test6 (
       sno NUMBER(10)
       CONSTRAINT check_sno
       CHECK(sno>=100 AND sno<=200)
     );

SQL> INSERT INTO test6 VALUES(10);
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_SNO) violated

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST6';
COLUMN_NAME     CONSTRAINT_NAME
-----------     ---------------
SNO             CHECK_SNO

SQL> ALTER TABLE TEST6
     DROP CONSTRAINTS CHECK_SNO;
Table altered.

SQL> SELECT column_name, constraint_name
     FROM user_cons_columns
     WHERE table_name = 'TEST6';
no rows selected

Enable and Disable Constraint

We can enable and disable the CHECK constraint using its name.

Syntax to disable a constraint,
ALTER TABLE tableName
DISABLE CONSTRAINT constraint_name;

Syntax to enable constraint,
ALTER TABLE tableName
ENABLE CONSTRAINT constraint_name;

Note:- We can only enable the constraint if the applied columns doesn’t contain the violated conditions.

Example:-

SQL> CREATE TABLE test7 (
       sno NUMBER(10)
       CONSTRAINT check_sno 
       CHECK(sno>=100 AND sno<=200)
     );
Table created.

SQL> INSERT INTO test7 VALUES(10);
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_SNO) violated

SQL> ALTER TABLE test7
     DISABLE CONSTRAINT check_sno;
Table altered.

SQL> INSERT INTO test7 VALUES(10);
SQL> SELECT * FROM test7;
       SNO
----------
        10

SQL> ALTER TABLE test7
     ENABLE CONSTRAINT check_sno;
ERROR at line 2:
ORA-02293: cannot validate (SCOTT.CHECK_SNO) 
- check constraint violated

SQL> UPDATE test7 SET sno = 120
     WHERE sno = 10;
1 row updated.

SQL> SELECT * FROM test7;
       SNO
----------
       120

SQL> ALTER TABLE test7
     ENABLE CONSTRAINT check_sno;
Table altered.

SQL> INSERT INTO test7 VALUES(10);
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CHECK_SNO) violated

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• Constraints in Oracle
• Rename Constraint
• Disable Constraint
• Drop Constraint

Search