Oracle Database - (Integrity) Constraints

1 - About

Constraints on tables.

Violation of constraint is not a syntax error but a run-time error.

3 - Five Types

3.1 - Primary

3.2 - UNIQUE

A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique—that is, no two rows of a table have duplicate values in a specified column or set of columns.

The Null value is still allowed.

3.3 - FOREIGN

A foreign key is a referential constraint between two tables. The foreign key identifies a column or a set of columns in one (referencing) table that refers to set of columns in another (referenced) table.

3.4 - Not Null

not null

3.5 - Check

CREATE TABLE employee
(
  emp_id NUMBER,
  emp_name,
  age NUMBER CHECK (age > 0)
);

Any valid SQL expression may be used.

4 - Constraint Clause

To understand how best to use constraints, you should first understand the basic purposes of constraints.

4.1 - Enable

In order to use a constraint for enforcement, the constraint must be in the ENABLE state. An enabled constraint ensures that all data modifications upon a given table (or tables) satisfy the conditions of the constraints. Data modification operations which produce data that violates the constraint fail with a constraint violation error.

4.2 - Validate

To use a constraint for validation, the constraint must be in the VALIDATE state. If the constraint is validated, then all data that currently resides in the table satisfies the constraint. Note that validation is independent of enforcement. Although the typical constraint in an operational system is both enabled and validated, any constraint could be validated but not enabled or vice versa (enabled but not validated).

When you can create a no validate constraint, you ask the database to not validate the data already present in the table.

4.3 - Rely

RELY (or Belief). In some cases, you will know that the conditions for a given constraint are true, so you do not need to validate or enforce the constraint. However, you may wish for the constraint to be present anyway to improve query optimization and performance. When you use a constraint in this way, it is called a belief or RELY constraint, and the constraint must be in the RELY. The RELY state provides you with a mechanism for telling Oracle that a given constraint is believed to be true.

5 - Constraint and

5.1 - Parallelism

All constraints can be validated in parallel. When validating constraints on very large tables, parallelism is often necessary to meet performance goals. The degree of parallelism for a given constraint operation is determined by the default degree of parallelism of the underlying table.

5.2 - Partitioning

You can create and maintain constraints before you partition the data.

6 - Management

6.1 - Creation

A constraint is created indirectly with the CREATE TABLE or ALTER TABLE statement. It's then not possible to use a CREATE CONSTRAINT expression.

6.2 - View Constraints

You can create constraints on views. The only type of constraint supported on a view is a RELY constraint.

This type of constraint is useful when queries typically access views instead of base tables, and the database administrator thus needs to define the data relationships between views rather than tables. View constraints are particularly useful in OLAP environments, where they may enable more sophisticated rewrites for materialized views.

7 - Reference

db/oracle/constraint.txt · Last modified: 2017/09/13 21:22 by gerardnico