Managing Constraints   «Prev  Next»

Lesson 2 Types of constraints
Objective Identify the four basic types of constraints.

Types of Table Constraints in Oracle

A constraint restricts the type of information that can be placed into a particular database object. Although you can define a constraint for either a table or a column, all constraints operate on the individual rows in a table. When you try to insert, update, or delete a row in a table that includes constraints, Oracle checks the constraint to ensure that the new values, or lack thereof, do not violate the specifications of the constraint.
There are four basic types of constraints you can use for a table:
  1. The PRIMARY KEY constraint
  2. The UNIQUE constraint
  3. The FOREIGN KEY constraint
  4. CHECK constraints
Each of these types of constraints is described in more depth in the following lessons. Each lesson includes a detailed explanation, an example, and a discussion of the particular constraint.

Syntax for constraints

Although constraints can be applied to either a table or a particular column, the basic syntax for the constraint is the same in either case.
The syntax is as shown in the following MouseOver:

  1. The CONSTRAINT keyword is required to identify a constraint.
  2. The constraint_name is the name of the particular constraint.
  3. The CONSTRAINT_TYPE is one of four reserved words that identifies the type of constraint.
  4. The constraint_description is dependent on the type of constraint. It could be as simple as a column name or include logical conditions.

Constraint Syntax Elements
The exact placement of this syntax and the difference between using a constraint on a column or a table are explained later in this module.
The next lesson explores the PRIMARY KEY constraint.

Oracle Constraint

An Oracle constraint is a rule or rules that you can define on one or more columns in a table to help enforce a business rule. For example, a constraint can enforce the business rule that an employee's starting salary must be at least $31,000.00. Another example of a constraint enforcing a business rule is to require that if a new employee is assigned a department (although they need not be assigned to a particular department right away), the department number must be valid and exist in the DEPT table. Six types of data integrity rules can be applied to table columns: null rule, unique column values, primary key values, referential integrity values, complex in-line integrity, and trigger-based integrity. We will touch upon each of these briefly in the following sections.
All the constraints on a table are defined either when the table is created or when the table is altered at the column level, except for triggers, which are defined according to which DML operation you are performing on the table. Constraints may be enabled or disabled at creation or at any point of time in the future; when a constraint is either enabled or disabled (using the keyword ENABLE or DISABLE), existing data in the table may or may not have to be validated (using the keyword VALIDATE or NOVALIDATE) against the constraint, depending on the business rules in effect.
For example, a table in an automaker's database named CAR_INFO containing new automobile data needs a new constraint on the AIRBAG_QTY column, where the value of this column must not be NULL and must have a value that is at least 1 for all new vehicles. However, this table contains data for model years before air bags were required, and as a result, this column is either 0 or NULL. One solution, in this case, would be to create a constraint on the AIRBAG_QTY table to enforce the new rule for new rows added to the table, but not to validate the constraint for existing rows. Here is a table created with all constraint types. Each type of constraint is reviewed in turn in the following subsections.

create table cust_order
(order_number number(6) primary key,
order_date date not null,
delivery_date date,
warehouse_number number default 12,
customer_number number not null,
order_line_item_qty number check (order_line_item_qty < 100),
ups_tracking_number varchar2(50) unique,
foreign key (customer_number) references customer(customer_number));