Oracle Databases Records: Check Constraints

	Check Constraints

Introduction

During data entry, you may want to restrict a range of values that are allowed on a column. In the same way, you can create a rule that must be respected on a combination of columns before the record can be created.

The ability to verify that one or more rules are respected on a table is called a check constraint. A check constraint is a Boolean operation performed by the interpreter. The interpreter examines a value that has just been provided for a column.

If the value is appropriate:

The constraint produces TRUE
The value gets accepted
The value is assigned to the column

If the value is not appropriate:

The constraint produces FALSE
The value gets rejected
The value is not assigned to the column

You create a check constraint at the time you are creating a table.

Creating a Check Constraint

To create a check constraint, create the column on which the constraint will apply. Before the closing parenthesis of the table definition, use the following formula:

CONSTRAINT name CHECK (expression)

The CONSTRAINT and the CHECK keywords are required. As an object, make sure you provide a name for it. Inside the parentheses that follow the CHECK operator, enter the expression that will be applied. Here is an example that will make sure that the hourly salary specified for an employee is greater than 12.50:

SQL> CREATE TABLE Employees
  2  (
  3     EmployeeNumber nchar(6),
  4     FirstName nvarchar2(20),
  5     LastName nvarchar2(20),
  6     HourlySalary number(6, 2),
  7     CONSTRAINT CHK_HourlySalary CHECK(HourlySalary > 12.50)
  8  );

Table created.

SQL>

With the constraint(s) in place, during data entry, if the user provides an invalid value, an error would display.