Data Integrity

It is important that data adheres to a predefined set of rules, as determined by the database administrator or application developer. An integrity constraint is a mechanism used by ORACLE to prevent invalid data entry into the base tables of the database. Integrity constraints can be defined to enforce the business rules that are associated with the information in a database. If any of the results of a AML statement .execution violate an integrity constraint, the statement is rolled back and an returned.

What is Data Integrity?

Data Integrity is the property of well designed table allows the physical and logical structure of a table to change without affecting applications that access the table.

Why use Data Integrity

Data integrity constraints are a means that Oracle uses to prevent invalid data entry into the database tables. These integrity constraints are defined to enforce the business rules that are associated with the information in a database. Oracle never violates these rules, thereby guaranteing data integrity.

Intergrity Constraints:

An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true:

If an integrity constraint is created for a table and some existing table data does not satisfy the constraint, the constraint cannot be enforced.

After a constraint is defined, if any of the results of DML statement violate the integrity constraint, the statement is rolled back and an error is returned.

Integrity constraints are defined with a table and are stored as part of the table's definition, centrally in the database's data dictionary, so that all database applications must adhere to the same set of rules. If a rule changes, it need only be changed once at the database level and not many times for each application.

Types of Integrity Constraints

The following integrity constraints are supported by ORACLE.
There are several types of integrity constraints that the database administrator or application developer can use to impose restrictions on the input of column values :

NOT NULL - disallows nulls (empty entries) in a table's column
UNIQUE - disallows duplicate values in a column or set of columns
PRIMARY KEY - disallows duplicate values and nulls in a column or set of columns
FOREIGN KEY - requires each value in a column or set of columns match a value in a related table's UNIQUE or PRIMARY KEY
CHECK - disallows values that do not satisfy the logical expression of the Constraint Keys

The term "Key" is used in the definitions of several types of integrity constraints. A key is the column or set of columns included in the definition of certain types of integrity constraints. Key describe the relationships between the different tables and columns of a relational database. The different types of keys include:

Primary Key : The column or set of columns include in the definition of a table's PRIMARY KEY constraint. A primary key's value uniquely identify the rows in a table. Only one primary key may be defined per table.

Unique Key: The column or set of columns included in the definition of a UNIQUE constraint.

Foreign Key : The column or set of columns included in the definition of a referential integrity constraint.

Referenced Key: The unique key or primary key of the same of different table that is referenced by a foreign key. Individual values in a key are called key values.

Enabling and Disabling Integrity constraints

An 'integrity constraint defined on a table can be either of two states: enabled or disabled. When a constraint ,is enabled the constraint rules are enforced as defined in the constraint definition Likewise, when the constraint is disabled, the constraint rules are not enforced.

If an attempt is made to enable a constraint on a table that does not adhere to the rules of the integrity constraints, Oracle generates an exceptions error and the issuing statement Is rolled back. If ;in exceptions exists, you cannot enable the constraint until it is updated or deleted. The create table and alter table commands are used to enable and disable constraints.

Dropping integrity Constraints

To drop an Integrity constraints, issue the alter table commands with the drop clause. In the following statement, the constraint is dropped from the MST_PARTS table:
alter table mst_parts
drop constraints s_par-ts_code-flk;

Advantages of Integrity Constraints

Integrity constraints are not the only way to enforce data integrity rules on the data of your database. You can also:

enforce business rules in the code of a database application use stored procedures to completely control access to data

enforce business rules using triggered stored database procedures

The following section describes some of the advantages that integrity constraints have verses these other alternatives.

1. Declarative Ease Because integrity constraints are defined using SQL commands, when you define or alter a table, no programming is required. Therefore, they are easy to write, eliminate programming errors, and their functionality is controlled by ORACLE. For these reasons, declarative integrity constraints are preferable to application code and database triggers.

2. Centralized Rules Integrity constraints are defined for tables and stored in the data dictionary. Therefore, the data entered by any application must adhere to the same integrity constraints associated with a table. By moving business rules from application code to centralized integrity constraints, the tables of a database are guaranteed to contain valid data, no matter which database application manipulates the information.

3. Maximum Application Development Productivity If a business rule enforced by an integrity constraint changes, the administrator need only change the definition of that integrity constraint and all applications automatically adhere to the modified constraint. Alternatively, if a business rule is enforced by the code of each database application, developers must modify all application source code and recompile, debug, and test the modified applications.

4. Immediate user Feedback Because specific information about each integrity constraint is stored in the data dictionary, database applications can be designed to use the information to provide immediate user feedback about integrity constraint violations, even before the SOL statement is executed and checked by ORACLE.

5. Superior Performance Because the semantics of integrity constraint declarations are clearly defined, performance optimizations are implemented for each specific declarative rule. The ORACLE query optimizer can use declarations to learn more about data to improve overall query performance.

6. Flexibility for Data Loads and Identification of Integrity Violations

Integrity constraints can be temporarily disabled so that large amounts of data can be loaded without the overhead of constraint checking. When the data load is complete, the integrity constraints can be easily enabled, and new rows that violate integrity constraints can be automatically reported to a separate exceptions table.

The performance cost of Integrity Constraints

The advantage of enforcing data integrity rules do not come without some loss in performance. In general, the "cost" of including integrity constraint is, at most, the same as executing a SOL statement that evaluates the constraint.

Domain Name Search