ORACLE CONSTRAINTS

ORACLE CONSTRAINTS

Constraints are rules for a database
that limit the acceptable data values for a table. They are the
optional schema objects that depend on a table. The existence of a
table without any constraint is possible, but the existence of a
constraint without any table is not possible. Constraints enforce the
business rules in a database. Constraints can be created along with the
table in the CREATE TABLE statement. Addition and deletion of
constraints can be done in the ALTER TABLE statement.







The following types of constraints are available in Oracle 10g:

• NOT NULL: It enforces that a column,
  declared as not null, cannot have any NULL values. For example, if an
  employee's hire date is not known, then that employee may not be
  considered as a valid employee. If a protected column does not possess
  any value, then the INSERT and UPDATE statements on those columns will
  fail.
• UNIQUE: It ensures that columns protected by this constraint cannot have duplicate values.
• PRIMARY KEY:
  It is responsible for uniquely identifying a row in a table. A table
  can have only one PRIMARY KEY constraint. A PRIMARY KEY constraint
  completely includes both the NOT NULL and UNIQUE constraints. It is
  enforced with an index on all columns.
• FOREIGN KEY:
  It is also known as referential integrity constraint. It enforces that
  values referenced in one table are defined in another table. It
  establishes a parent-child or reference-dependent relationship between
  the two tables. It cannot be created on columns of type CLOB, NCLOB,
  BLOB, LONG, LONG RAW, and TIMESTAMP WITH TIMEZONE.
• CHECK:
  It enforces that columns must meet a specific condition that is
  evaluated to a Boolean value. If the value evaluates to false, then the
  database will raise an exception, and not allow the INSERT and UPDATE
  statements to operate on columns.

How to enable a constraint



A constraint is enabled using the ENABLE clause of the ALTER TABLE
statement. When enabling a constraint, the VALIDATE or NOVALIDATE
keyword can be used with the ENABLE clause.







The VALIDATE keyword specifies that all existing rows in the table and
new rows inserted in the table must comply with the constraint.
Therefore, when enabling a constraint by using the ENABLE VALIDATE
clause, if any row in the table violates the constraint, the constraint
remains disabled and Oracle returns an error. If all rows comply with
the constraint, Oracle enables the constraint. Subsequently, if a new
row being inserted in the table does not comply with the constraint,
the row insertion fails and Oracle returns an error.







The NOVALIDATE keyword specifies that the existing rows in the table
may violate the constraint. However, new rows inserted in the table
must comply with the constraint.





Note:

• Creating a PRIMARY KEY or UNIQUE constraint for a column automatically creates a unique index for the column.
• When enabling a constraint, if a keyword is not specified with the ENABLE clause, it is taken as VALIDATE.

How to disable a constraint



A constraint is disabled using the DISABLE clause of the ALTER TABLE
statement. When disabling a constraint, the VALIDATE or NOVALIDATE
keyword can be used with the DISABLE clause.







The VALIDATE keyword specifies that the constraint is disabled and all
indexes used to enforce the constraint are dropped. However, the
constraint still remains valid. In this state, Data Manipulation
Language (DML) statements are not allowed on the table. This is because
new changes in the table cannot be verified, as the constraint is
disabled, and the existing data in the table has to comply with the
constraint, as the constraint is still valid.







The NOVALIDATE keyword specifies that the constraint is disabled and
invalidated. The existing data in the table and new data being inserted
in the table may violate the constraint.







Note: When disabling a constraint, if a keyword is not specified with the DISABLE clause, it is taken as NOVALIDATE.



Types of constraints



Some of the constraints for designing files and filegroups are as follows:

• A file or filegroup cannot be used by more than one database.
• A file can be a member of only one filegroup.
• Data and transaction log information cannot be part of the same file or filegroup.
• Transaction log files are not part of any filegroup.

States of a constraint



A constraint can have the following four states:

• ENABLE VALIDATE: This state specifies that the
  constraint is enabled, and the existing data in the table protected by
  the constraint conforms to the constraint. It also ensures that new
  data being entered into the table also conforms to the constraint.
• ENABLE NOVALIDATE:
  This state specifies that the constraint is enabled, but the existing
  data in the table protected by the constraint may violate the
  constraint. However, it ensures that new data being entered into the
  table conforms to the constraint.
• DISABLE VALIDATE:
  This state specifies that the constraint is disabled but still remains
  valid. In this state, no Data Manipulation Language (DML) operation is
  allowed on the table protected by the constraint.
• DISABLE NOVALIDATE: This state specifies that the constraint is disabled and invalidated.

Functions of a constraint



Following are the functions of a constraint:

• Constraints enforce rules on data in a table whenever a row is inserted, updated, or deleted from the table.
• Constraints prevent the deletion of a table if there are dependencies from other tables.
• Constraints enforce rules at the column level as well as at the table level.

Rules and constraints for creating filegroups



The rules and constraints for creating filegroups are as follows:

• The first (or primary) data file must reside on the primary filegroup.
• All system files can be placed on the primary filegroup.
• A file cannot be a member of more than one filegroup at a time.
• Filegroup can be allocated indexes, tables, text, and image data.
• New data pages are not automatically allocated to user-defined filegroups if the primary filegroup runs out of space.

The
article has covered every possible aspect of an Oracle constraint.
After reviewing this article, you will be well versed in the usage of
Oracle constraints.

本文转自

http://www.ucertify.com/article/ORACLE-CONSTRAINTS.html