ORACLE CONSTRAINTS

Are you preparing for IT certification? With practice questions, study notes, interactive quizzes, tips and technical articles, uCertify PrepKits ensure that you get a solid grasp of core technical concepts to ace your certification exam in first attempt.

ORACLE CONSTRAINTS

Rating:

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.

Rating: