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.