SQL CHECK Constraint

Check constraint is used to validate values entered into a column. CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are placed in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints get the list of valid values from another table.

CHECK constraints determine the valid values from a logical expression that is not based on data in another column. For example, it is possible to limit the range of values for a salary column by creating a CHECK constraint that allows only data that ranges from 15,000 through 500, 00. This prevents salaries from being entered beyond the normal salary range.

 You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators.

Syntax:

CONSTRAINT constrain_name CHECK (predicate),

COLUMN LEVEL

SQL> CREATE TABLE Persons (P_Id number (6) NOT NULL CHECK (P_Id>0),LastName varchar2 (15) NOT NULL,FirstName varchar2 (15),Address varchar2 (25),City varchar (15) check (city in (‘Ferozepur’,‘Jalandhar’,‘Nabha’)));

Table created.

TABLE LEVEL

SQL> CREATE TABLE Persons(P_Id number (6) NOT NULL,LastName varchar2 (15) NOT NULL,FirstName varchar2 (15),Address varchar2(25),City varchar2 (13),CONSTRAINT ck_Person CHECK (P_Id>0 AND City=‘Ferozepur’));

 TIPS:

• You can use any number of constraints in a table.

• A single column can have any number of check constraints.