Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

4.5 Constraints

A constraint
enforces certain aspects of
data integrity within a database.
When you add a constraint to a particular column, Oracle
automatically ensures that data violating that constraint is never
accepted. If a user attempts to write data that violates a
constraint, Oracle returns an error for the offending SQL statement.

Constraints may be associated with columns when you create or add the
table containing the column (via a number of keywords) or after the
table has been created with the SQL statement ALTER TABLE. Five
constraint types are supported by Oracle8 and later versions:

NOT NULL

You can designate any column as NOT NULL. If any SQL operation leaves
a NULL value in a column with a NOT NULL constraint, Oracle returns
an error for the statement.

Unique

When you designate a column or set of columns as unique, users cannot
enter values that already exist in another row in the table for those
columns.

The unique
constraint is implemented by the creation of an index, which requires
a unique value. If you include more than one column as part of a
unique key, you will create a single index that will include all the
columns in the unique key. If an index already exists for this
purpose, Oracle will automatically use that index.

If a column is unique but allows NULL values, any number of rows can
have a NULL value, because the NULL indicates the absence of a value.
To require a unique value for a column in every row, the column
should be both unique and NOT NULL.

Primary key

Each table can have, at most, a single primary key constraint. The primary key may
consist of more than one column in a table.

The primary key constraint forces each primary key to have a unique
value. It enforces both the unique constraint and the NOT NULL
constraint. A primary key constraint will create a unique index, if
one doesn't already exist for the specified
column(s).

Foreign key

The foreign key
constraint is defined for a
table (known as the
child) that has a relationship with another
table in the database (known as the parent). The
value entered in a foreign key must be present in a unique or primary
key of another specific table. For example, the column for a
department ID in an employee table might be a foreign key for the
department ID primary key in the department table.

A foreign key can have one or more columns, but the referenced key
must have an equal number of columns. You can have a foreign key
relate to the primary key of its own table, such as when the employee
ID of a manager is a foreign key referencing the ID column in the
same table.

A foreign key can contain a NULL value if it's not
forbidden through another constraint.

By requiring that the value for a foreign key exist in another table,
the foreign key constraint enforces referential integrity in the
database. Foreign keys not only provide a way to join related tables
but also ensure that the relationship between the two tables will
have the required data integrity.

Normally, you cannot delete a row in a parent table if it causes a
row in the child table to violate a foreign key constraint. However,
you can specify that a foreign key constraint causes a
cascade delete,
which means that deleting a referenced row in the parent table
automatically deletes all rows in the child table that reference the
primary key value in the deleted row in the parent table.

Check

A check
constraint is a more general-purpose constraint. A check constraint
is a Boolean expression that evaluates to either TRUE or FALSE. If
the check constraint evaluates to FALSE, the SQL statement that
caused the result returns an error. For example, a check constraint
might require the minimum balance in a bank account to be over $100.
If a user tries to update data for that account in a way that causes
the balance to drop below this required amount, the constraint will
return an error.

Some constraints require the creation of indexes to support them. For
instance, the unique constraint creates an implicit index used to
guarantee uniqueness. You can also specify a particular index that
will enforce a constraint when you define that constraint.

All constraints can be either immediate or deferred. An
immediate
constraint is enforced as soon as a write operation
affects a constrained column in the table. A
deferred
constraint is enforced when the SQL statement that caused
the change in the constrained column completes. Because a single SQL
statement can affect several rows, the choice between using a
deferred constraint or an immediate constraint can significantly
affect how the integrity dictated by the constraint operates. You can
specify that an individual constraint is immediate or deferred, or
you can set the timing for all constraints in a single transaction.

Finally, you can temporarily suspend the enforcement of constraints
for a particular table. When you reenable the operation of the
constraint, you can instruct Oracle to validate all the data for the
constraint or simply start applying the constraint to the new data.
When you add a constraint to an existing table, you can also specify
whether you want to check all the existing rows in the
table.