SQL Check Constraint With Example

Constraints Definition:

SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

Constraints provide a standard mechanism to maintain the accuracy and integrity of the data inside a database table.

6. CHECK Constraints

The CHECK constraint is used to restrict the values that can be placed in a column.

SQL CHECK Constraint must be specified as a logical expression that evaluated specific condition either TRUE or FALSE.

Syntax:

CREATE TABLE table_name(
    column_name datatype[(size)] [ NULL | NOT NULL ] CHECK (column_name condition),
    column_name datatype[(size)] [ NULL | NOT NULL ] CHECK (column_name condition),
    ....
);

Example:

SQL> CREATE TABLE std_info(
    no NUMBER(3) PRIMARY KEY,
    stu_code VARCHAR(10) CHECK (stu_code like 'j%'),
    name VARCHAR(30) CHECK ( name = upper(name) ),
    city VARCHAR(30) CHECK (city IN ('DELHI','MUMBAI','BANGALORE','CENNAI'))
    scholarship NUMBER(5) CHECK (scholarship BETWEEN 5000 AND 20000)
);
------------------------
Table created.

SQL> INSERT INTO std_info VALUES (1,'j001', 'PRAYAG VERMA', 'CHENNAI', 8900);
-----------------------
1 row created.

We have created new std_info table name with following check constraints:

  • Values inserted into stu_code column must be start with the lower letter 'j'.
  • Values inserted into name column must be capitalize.
  • Values inserted into city column only allow 'Delhi','Mumbai','Bangalore','Chennai' as valid legitimate values.
  • Values inserted into scholarship column between 5000 and 20000.

Read Also: