Managing Constraints   «Prev 

Integrity Constraint

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 Syntax for Oracle Tables


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