6.3 How do I…Add a column check constraint?Problem
In our systems, we want to ensure that the values in some columns fall within allowable ranges. Our applications can verify the values within the data, but I want to ensure that no invalid data can be created. I have heard that column check constraints can be used to easily enforce integrity rules at the column level. How do I use column check constraints to validate data?
Technique
When a table is created using the CREATE TABLE statement, a CHECK clause can be specified after a column to create a column check constraint. The syntax is:
CREATE TABLE tablename (
columnname datatype [CONSTRAINT constraint_name] CHECK (condition));
The CONSTRAINT clause is optional and can be used to create a named constraint. The condition following the CHECK keyword is a Boolean expression that is evaluated using the values in the row being inserted or updated. Column check constraints are very flexible to use, but there are some restrictions that should be kept in mind while designing column check constraints. Restrictions on expressions in a column check constraint are as follows:
4 A column check constraint expression cannot reference other columns.
4 Use of sequences and queries is disallowed in the expression.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Run the SQL in the CHP6_9.SQL file, as shown in Figure 6.9, which contains a statement to create a table with a column check constraint.
Line 1 contains the CREATE TABLE keywords used to create a new table. Lines 2 through 4 define the columns that make up the table. The CHECK clause in line 5 requires that the value of the SAL column be less than 10,000 whenever a record is inserted or updated. Note that there is no comma after the SAL column definition and the check constraint is a part of it. A named constraint is created as a result of the CONSTRAINT clause before the CHECK keyword. It is generally a good practice to name constraints whenever possible, for easier tracking of constraints in the database. Check constraints can be viewed by querying the USER_CONSTRAINTS data dictionary view, which is discussed in How-To 6.5.
2. Run the CHP6_10.SQL file in SQL*Plus. The INSERT statement contained in the file attempts to insert a row into the BONUS06 table. The statement will fail because the value being inserted into the SAL column is such that the expression defining the column check constraint evaluates to FALSE. Figure 6.10 shows the results of running the statement in SQL*Plus.
The values clause in lines 3 and 4 attempt to insert a row into the BONUS06 table with a value of the SAL column greater than that is allowed by the constraint. This results in an ORA-02290 error as a value in the SAL column violates the column check constraint created in Step 1.
Note-The Boolean expression defining a check constraint can evaluate to true, false, or unknown. The constraint is violated only if the expression evaluates to false, and not with true or unknown. If a value of NULL was used as a value for the SAL column in this example, the expression evaluates to unknown, and the row will be inserted. To avoid conditions like this, a NOT NULL constraint should be placed before the CHECK keyword:
SAL NUMBER(10,2) NOT NULL CHECK (SAL < 10000)
Alternatively, the constraint can also be written as:
SAL NUMBER(10,2) CHECK (SAL < 10000 AND SAL IS NOT NULL)
How It Works
Step 1 creates a table with a column check constraint. A column check constraint can be created for columns only with a CREATE TABLE statement and not with an ALTER TABLE statement. Step 2 attempts to insert an invalid row into the table. The error message displayed in Step 2 contains the constraint name created when the constraint was added. A NOT NULL constraint is a type of a column CHECK constraint.
Comments
A column check constraint restricts the range of valid values for a column. Multiple column check constraints are allowed with any column definition. The check expression defining the check constraint cannot reference any other columns in the same or another table. Check constraints force the database to do some of the work your applications normally would do. If an application fails to check for invalid values, you can be assured that the database will catch the error. Table check constraints enforce more sophisticated integrity rules on multiple columns and are discussed in the next How-To.
Comments
A column check constraint restricts the range of valid values for a column. Multiple column check constraints are allowed with any column definition. The check expression defining the check constraint cannot reference any other columns in the same or another table. Check constraints force the database to do some of the work your applications normally would do. If an application fails to check for invalid values, you can be assured that the database will catch the error. Table check constraints enforce more sophisticated integrity rules on multiple columns and are discussed in the next How-To.