There are many situations when you want specific values to be stored in a table column rather that allowing some random values. In case you want to check whether correct values are going in the data apply the CHECK constraint. Each newly inserted or updated value of such column must take value according to the condition specified in the CHECK constraint.
The check constraint can be applied to single column or a group of columns. In case you are defining CHECK constraint on a set of columns, the values stored in that group of columns must follow the condition(s). For example for columns house number, street, city, state and zip code you can specify the condition in one CHECK constraint at table level.
How to Add CHECK Constraint to Table
CHECK constraint can be added as column level or table level constraint.
Syntax for Column Level CHECK Constraint
Column-name DATATYPE(size) [CONSTRAINT constraint-name] CHECK (condition using logical operators or functions),
- Column-Name is the name of the column that you want to check at the time of data insertion or update
- CONSTRAINT Constraint-name- the keyword and the name of the CHECK constraint (this is optional)
- CHECK keyword
- Condition defined as an expression using the name of the column, comparison operators and Logical operators
Column Level Check Constraint without Constraint Name
CREATE TABLE SalesTbl ( SPID number(6) PRIMARY KEY, SPNAME varchar2(30), SPCITY varchar2(30) DEFAULT 'New York', SPCOMM number(2,2) CHECK (SPCOMM>0.2) );
Column Level Check Constraint with Constraint Name
CREATE TABLE SalesTbl ( SPID number(6) PRIMARY KEY, SPNAME varchar2(30), SPCITY varchar2(30) DEFAULT 'New York', SPCOMM number(2,2) CONSTRAINT chk_comm CHECK (SPCOMM>0.2) );
Column Level Multiple Check Constraints with Constraint Names (using IN operator and > )
CREATE TABLE SalesTbl ( SPID number(6) PRIMARY KEY, SPNAME varchar2(30), SPCITY varchar2(30) CONSTRAINT chk_city CHECK (spcity in('New York','Chicago','Washington')), SPCOMM number(2,2) CONSTRAINT chk_comm CHECK (SPCOMM>0.2) );
Column Level multiple Check Constraints with Constraint Names (using IN, LIKE operator, > and OR )
CREATE TABLE SalesTbl ( SPID number(6) PRIMARY KEY, SPNAME varchar2(30) CONSTRAINT chk_name CHECK (SPNAME like 'Ar%' OR SPNAME like 'To'), SPCITY varchar2(30) CONSTRAINT chk_city CHECK (spcity in('New York','Chicago','Washington')), SPCOMM number(2,2) CONSTRAINT chk_comm CHECK (SPCOMM>0.2) );
Output
insert into salestbl values (1,’Arnold’,’New York’, 0.3) Success
insert into salestbl values (1,’Sam’,’New York’, 0.3) Failure – ORA-02290: check constraint (SQL_AZTQXNRCVVDBWUBYGJSAAMQSA.CHK_NAME) violated ORA-06512: at “SYS.DBMS_SQL”, line 1721
insert into salestbl values (1,’Tony’,’San Fransisco’, 0.3) Failure- error- ORA-02290: check constraint (SQL_AZTQXNRCVVDBWUBYGJSAAMQSA.CHK_CITY) violated ORA-06512: at “SYS.DBMS_SQL”, line 1721
insert into salestbl values (1,’Tonim’,’Chicago’, 0.1) Failure- ORA-02290: check constraint (SQL_AZTQXNRCVVDBWUBYGJSAAMQSA.CHK_COMM) violated ORA-06512: at “SYS.DBMS_SQL”, line 1721
Syntax for Table Level CHECK Constraint
In this type the constraint is added at the end of table definition after the last column declaration. The table level check constraint can be on single column or multiple columns.
(Table definition,
[CONSTRAINT constraint-name] CHECK (condition for column-name1, condition for column-name 2…)
- CONSTRAINT Constraint-name- the keyword and the name of the CHECK constraint (this is optional)
- CHECK keyword
- Conditions defined as an expression using the name of the columns, comparison operators and Logical operators
Without Constraint Name
CREATE TABLE SalesTbl ( SPID number(6) PRIMARY KEY, SPNAME varchar2(30), SPCITY varchar2(30) , SPCOMM number(2,2), CHECK (SPCOMM>0.2 and SPCITY in('New York','Chicago','Washington')) );
With Constraint Name
CREATE TABLE SalesTbl ( SPID number(6) PRIMARY KEY, SPNAME varchar2(30), SPCITY varchar2(30) , SPCOMM number(2,2), CONSTRAINT chk_common CHECK (SPCOMM>0.2 and SPCITY in('New York','Chicago','Washington')) );
Output
insert into salestbl values (1,’Tonim’,’Chicago’, 0.4) Success
insert into salestbl values (1,’Tonim’,’Chicago’, 0.1) Failure – ORA-02290: check constraint (SQL_AZTQXNRCVVDBWUBYGJSAAMQSA.CHK_COMMON) violated ORA-06512: at “SYS.DBMS_SQL”, line 1721
insert into salestbl values (1,’Tonim’,’Ohio’, 0.4) Failure-ORA-02290: check constraint (SQL_AZTQXNRCVVDBWUBYGJSAAMQSA.CHK_COMMON) violated ORA-06512: at “SYS.DBMS_SQL”, line 1721
CREATE TABLE SalesTbl ( SPID number(6) PRIMARY KEY, SPNAME varchar2(30), SPCITY varchar2(30) , SPCOMM number(2,2), SPPHONE varchar2(15), SPDOJ DATE, CONSTRAINT chk_common CHECK (SPCOMM>0.2 and SPCITY in('New York','Chicago','Washington') and length(SPPHONE)>10 and SPDOJ BETWEEN '31-DEC-1990' and '01-JAN-2017') );
Points to Remember
- You can create multiple Check constraints in one table. They can be defined individually for different columns.
- You can create single complex CHECK constraint at table level defining condition for different conditions combined with AND or OR.
- You can use all comparison operators =,<>,>,>=, <,<=, LIKE, IN, and BETWEEN operators
- You cannot use column name of same or another table in condition expression in CHECK with column level constraint. You can use the column names of same table in table level CHECK constraint.
- You cannot use special keywords like USER,ROWNUM and ROWID in CHECK constraint