6.4 How do I…Create a table constraint?

Problem

I need to use check constraints on tables to reduce redundant validation code from my applications. I want to create table check constraints to perform these validation checks automatically in the database, whenever a statement inserts or updates a row in the table. I know that column check constraints can be used on individual columns, but I want to specify a business rule that spans multiple columns of the table. How do I use table constraints to validate data?

Technique

When a table is created using the CREATE TABLE statement, a table constraint can be specified independently, that is, not a part of any column definition. Consequently, multiple columns can be included in the definition of a table constraint. Table constraints and column definitions can appear in any order, such as the following:

CREATE TABLE tablename (

column_definition,

table_constraint)

The table_constraint while defining a table check constraint contains the following:

[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, but unlike the column check constraint; a table check constraint expression can reference any columns in the table in which it is defined. Any column constraint can be defined using the table constraint syntax. A NOT NULL constraint can be implemented as a CHECK constraint by defining a table check constraint using the CHECK COLUMN_NAME IS NOT NULL syntax. For restrictions on check constraints, refer to the previous How-To. An ALTER TABLE statement allows table constraints to be created for an existing table.

Steps

1. Run SQL*Plus and connect as the WAITE user account. Run the CHP6_11.SQL file in SQL*Plus, as shown in Figure 6.11. The file contains a statement to create a table with a table 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 defines a table check constraint that enforces a rule on the two salary columns in the same expression, to specify a range of valid salaries. The expression within parentheses must evaluate to TRUE for a record to be inserted or updated. As you can see, multiple table columns can be included in the check expression of a table check constraint. Note the comma at the end of line 4, preceding the CHECK clause. If the comma is omitted an ORA-02438 error would occur, as Oracle would treat it as a column check constraint and column constraints cannot reference other columns. When the table is created, a constraint will be added to the USER_CONSTRAINTS data dictionary view and given a name generated by the database. To create a named table check constraint, simply use a CONSTRAINT clause before the CHECK clause. Check constraints can be viewed by querying the USER_CONSTRAINTS data dictionary view.

2. Run SQL*Plus and connect as the WAITE user account. Run the CHP6_3.SQL file to create the COMPANY06 table if you do not have this table in your current schema. Now run the commands in the CHP6_12.SQL file. The existing constraint on the COMPANY06 table is dropped, and then an ALTER TABLE statement is issued to add a table check constraint to the COMPANY06 table. Remember, an ALTER statement can be used to create table constraints and not column constraints. Figure 6.12 shows the results of running the two ALTER TABLE statements in SQL*Plus.

Using the ALTER TABLE syntax, the existing column constraint on the COMPANY06 table (which was created in Step 3 of How-To 6.1) is dropped before re-creating it as a table constraint. Line 1 contains the ALTER TABLE keywords used to modify a table. Line 2 contains the ADD CONSTRAINT clause used to add a named primary key as a table constraint. While creating a primary key, an index is also created for it. Line 3 dictates the tablespace name (following the USING INDEX keywords) to be used for creating the index. Lines 4 through 7 are the storage specifications for the index being created. The USING INDEX and STORAGE clauses are optional. Note the explicit tablespace and storage specifications for the index that needs to be created for the primary key. Similar specifications can be included with a primary or unique key definition as a column constraint. In either case, if such a specification is omitted, Oracle will automatically create indexes in the default tablespace using the default storage specification.

3. Run the CHP6_13.SQL file. 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 negative, which makes the expression defining the table check constraint evaluate to FALSE. Figure 6.13 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 negative value for the SAL column, and this is pro-hibited by the constraint. This results in an ORA-02290 error as a value in the SAL column violates the table check constraint created in step 2.

How It Works

Step 1 creates a table with a table check constraint. A table check constraint can be created within a CREATE TABLE statement. Step 2 creates a table check constraint on an existing table using an ALTER TABLE statement with an ADD CONSTRAINT clause. The constraint is added at the table level, because check constraints on columns cannot be created with the ALTER TABLE statement. Step 3 attempts to insert an invalid row into the table. The error message displayed in Step 3 contains the constraint name created when the constraint was added. The note in the previous How-To, where the Boolean expression defining the constraint evaluates to unknown with NULL values applies to table check constraints as well, and care should be taken to avoid such situations.

In Steps 1 and 2 of How-To 6.1, the composite primary key on the CUST06 table is defined at the table level as a table constraint. The columns comprising the primary key are defined before defining the CUST06 table’s primary key as a table constraint. The constraint definition could have preceded column definitions comprising the composite primary key, as column definitions and table constraint definitions can appear in any order.

Comments

Composite primary, foreign, and unique keys have to be implemented as table constraints. Table constraints can reference multiple columns. A table can have multiple FOREIGN KEY and CHECK constraints. A single column can be referenced by several table constraints in that table. Multiple check constraints should be designed carefully to have no conflicts within themselves. It is better to have multiple check constraints, each with a simple expression rather than having a single check constraint with a complex expression. If there is an integrity violation, an error message is returned by Oracle identifying the constraint. This error message can be used to quickly identify the violated business rule as each constraint implements a single business rule.

Whenever integrity rules can be evaluated based on logical expressions, check constraints should be used. Triggers (Chapter 13) can also be used to enforce complex business constraints that are not definable using declarative constraints.