Oracle provides two types of table indexes: unique and nonunique. Unique indexes enforce primary key and unique constraints. Nonunique indexes can improve query performance. Both types of indexes are implemented internally via a B*-tree data structure. A B*-tree data structure is graphically depicted as a balanced, inverted tree in which each leaf represents an index value. Understanding the following concepts is critical when you design an application's database:

• An ORDER BY clause in a SELECT statement can reference any column in a table—whether or not the table has an existing index based on that column.
• An index cannot be composed of more than 16 columns.
• An index doesn't store NULL values.
• SQL does not have a statement that will enable you to inspect the contents of an index.
• An index can be created only for a table, not a view.

Creating an Index

Here is the basic syntax to use when creating an index:

CREATE [UNIQUE] INDEX index-name
ON table-name (column1, ... columnN);

The variables are defined as follows:

index-name is the name to be given to the index (subject to Oracle database object naming restrictions).
table-name is the table for which the index is created.
column1 through columnN are the columns to be used in creating the index.

Notice that the keyword UNIQUE is optional. If you don't include UNIQUE, the created index is nonunique. In other words, the nonunique index does not restrict the values of a set of columns in any way. If you include the keyword UNIQUE, the index prevents a duplicate set of column values from being stored in the table.

Listing 4.26 provides an example. The primary key of the Student table is Student_ID. However, you'll frequently query the Student table based on a student's last name. To improve the performance of those queries, you create a nonunique index.

Listing 4.26. Creating a nonunique index.

SQL> create index Student_Last_Name on
  2  Student (Last_Name);

Index created.

Why You Shouldn't Create Unique Indexes

Although CREATE UNIQUE INDEX is a legal Oracle statement, you shouldn't use it; instead, declare PRIMARY KEY and UNIQUE constraints. The two principal reasons for this advice are these:

• When you declare PRIMARY KEY and UNIQUE constraints, Oracle automatically creates the appropriate unique index to enforce the constraints.
• You won't be able to declare a FOREIGN KEY constraint unless you also declare a corresponding PRIMARY KEY constraint.

Viewing Constraints

When you declare a column as NOT NULL, Oracle treats the mandatory requirement as a constraint. In fact, this constraint is the only one that can be seen with the SQL*Plus DESCRIBE command. Listing 4.27 provides an example. If you describe the Instructor table in SQL*Plus, Oracle indicates that the Instructor table has three mandatory columns. This number is somewhat misleading because Instructor_ID was defined as the table's primary key—which is automatically NOT NULL.

Listing 4.27. Viewing NOT NULL constraints with SQL*Plus DESCRIBE.

SQL> describe Instructor
 Name                           Null?    Type
 ------------------------------ -------- ----
 INSTRUCTOR_ID                  NOT NULL VARCHAR2(20)
 DEPARTMENT_ID                  NOT NULL VARCHAR2(20)
 LAST_NAME                      NOT NULL VARCHAR2(25)
 FIRST_NAME                              VARCHAR2(25)
 MI                                      VARCHAR2(1)
 POSITION                                VARCHAR2(25)
 TELEPHONE                               VARCHAR2(10)
 FAX                                     VARCHAR2(10)
 EMAIL                                   VARCHAR2(100)

You can use the Oracle data dictionary view named USER_CONSTRAINTS to see the constraints associated with a table. The columns returned by USER_CONSTRAINTS include

• CONSTRAINT_NAME
• CONSTRAINT_TYPE
• SEARCH_CONDITION

Listing 4.28 displays the results of a query of USER_CONSTRAINTS for all constraints associated with the Instructor table: two NOT NULL constraints, a CHECK constraint on the Position column, a primary key constraint that is indicated by a value of P for Constraint_Type, and a foreign key on Deparment ID.

Listing 4.28. Viewing constraints on the Instructor table.

SQL> select constraint_name, constraint_type, search_condition
  2  from user_constraints
  3  where
  4  table_name = `INSTRUCTOR';

Restricting Values with a Column CHECK
Constraint

The CHECK constraint is a column-level constraint that serves at least two purposes.

• Some columns—often numeric—do not have a set of discrete values that can be referenced with a foreign key. However, you still need to constrain the value of such a column to a particular range.
• The legal range of a column is not constant; it must be calculated when the column value is inserted or updated.

The CHECK constraint is declared in a CREATE TABLE or ALTER_TABLE statement using this syntax:

column-name datatype [CONSTRAINT
constraint-name] [CHECK (condition)]

The variables are defined as follows:

column-name is the column name.
datatype is the column's datatype, width, and scale.
constraint-name is the constraint name subject to Oracle database object-naming restrictions.
condition is a legal Oracle SQL condition that returns a Boolean value.

To illustrate this concept, Listing 4.29 shows how you can create a table that is used in a hospital database to store patient information. One of the columns in this table is the patient's body temperature in degrees Fahrenheit. You should restrict the possible values of this column by defining it as NUMBER(4,1). But this column still accepts numbers from 0.0 to 999.9—including some obviously nonsensical values for body temperature. You can use a CHECK constraint to restrict the range of the value to between 60.0 (for patients suffering from hypothermia) and 110.0.