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 diagram.
  

  CONSTRAINT constraint_name CONSTRAINT_TYPE
  constraint_description
  

The above statements holds true for Oracle 11g and "cloud-enabled" databases from versions 12c to 19c.
These versions support the same four basic types of constraints:

1. PRIMARY KEY constraint: Ensures that the specified column(s) contain unique, non-null values that uniquely identify a record in the table.
2. UNIQUE constraint: Ensures that all values in a specified column or set of columns are unique across the table, but allows null values (unless combined with a NOT NULL constraint).
3. FOREIGN KEY constraint: Enforces a relationship between columns in two tables, ensuring that values in the referencing (child) table match values in the referenced (parent) table.
4. CHECK constraint: Specifies a condition that each row in the table must satisfy, ensuring data integrity based on custom rules.

These constraints are foundational to Oracle Database's data integrity features and are applicable across on-premises and cloud-enabled deployments in versions up to 19c.

An integrity constraint is a rule that restricts the range of valid values for a column. It is placed on a column when the table is created. For the syntax, see CONSTRAINTS. If you do not name a constraint, Oracle assigns a name in the form SYS_Cn, where n is an integer. An Oracle-assigned name will usually change during an import, while a user-assigned name will not change. NULL permits NULL values. NOT NULL specifies that every row must have a non-NULL value for this column. UNIQUE forces column values to be unique. There can be only one PRIMARY KEY constraint on a table. If a column is UNIQUE it cannot also be declared the PRIMARY KEY (PRIMARY KEY also enforces uniqueness). An index enforces the UNIQUE or PRIMARY KEY, and the USING INDEX clause and its options specify the storage characteristics of that index. See CREATE INDEX for more information on the options. REFERENCES identifies this column as a foreign key from [user.]table [(column)]. Omitting column implies that the name in the user.table is the same as the name in this table. Note that when REFERENCES is used in a table_constraint (described shortly), it must be preceded by FOREIGN KEY. This is not used here, as only this column is referenced; table_constraint can reference several columns for FOREIGN KEY. ON DELETE CASCADE instructs Oracle to maintain referential integrity automatically by removing foreign key rows in the dependent tables if you remove the primary key row in this table.
CHECK assures that the value for this column passes a condition such as this:

Amount number(12,2) CHECK (Amount >= 0)	

condition may be any valid expression that tests TRUE or FALSE. It can contain functions, any columns from this table, and literals.
The EXCEPTIONS INTO clause specifies a table into which Oracle puts information about rows that violate an enabled integrity constraint. This table must be local. The DISABLE option lets you disable the integrity constraint when you create it. When the constraint is disabled, Oracle does not automatically enforce it. You can later enable the constraint with the ENABLE clause in ALTER TABLE. You can also create constraints at the table level. Table constraints are identical to column constraints except that a single constraint can reference multiple columns. For example, in declaring a set of three columns as a primary or foreign key.

CONSTRAINT constraint_name CONSTRAINT_TYPE
constraint_description

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.

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));