Oracle Database - (Integrity) Constraints

Card Puncher Data Processing

About

Constraints on tables.

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

Five Types

Primary

See Oracle Database - Primary Key

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.

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.

Not Null

not null

Check

create table employee
(
  emp_id number,
  emp_name,
  age number check (age > 0)
);

Any valid SQL expression may be used.

Constraint Clause

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

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.

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.

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.

Constraint and

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.

Partitioning

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

Management

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.

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.

Reference





Discover More
Owb Table Operator Data Rule Properties
OWB - Table

The Table operator enables you to source data from and load data into tables stored You can set Target Filter for Update, Target Filter for Delete, and Match By Constraint. When a data...
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Card Puncher Data Processing
Oracle Database - FOREIGN KEY Constraints

A foreign key is a referential constraint between two tables. The foreign key identifies: a column or a set of columns in one (Child/dependent) table that refers to set of columns in another...
Card Puncher Data Processing
Oracle Database - Primary Key

In Oracle Database, a Primary Key is a constraint implemented as: a “not null” constraint combined with an “Unique” constraint . A table may have only one primary key. A composite...
Data System Architecture
Relational Data Modeling - Unique (Constraint|Key)

Unique key implementation in a relational environment A unique constraint is a constraint that enforces the Entity Integrity e.g. that every value in a column or set of columns (key) be unique—that...



Share this page:
Follow us:
Task Runner