Managing Data Integrity

Overview

Enforcing Constraints

Maintaining Constraints

Overview

Data integrity guarantees that data adheres to business rules. There are three primary ways achiveing this:

1. Declarative integrity constraints. Integrity constraints include the following types:
   • NOT NULL.  Specify a column cannot contain null values (no value is assigned).
   • UNIQUE.  Indicate the values of a column (or columns) must be unique.
   • PRIMARY KEY. Designate a column (or columns) as the table's primary key.
   • FOREIGN KEY. Designate a column (or columns) as the foreign key in a referential integrity constraint.
   • CHECK. Specify a condition that each row of the table must satisfy.
2. Application Codes. Application codes may be implemented either as stored procedures within database (to be covered in the Chapter on PL/SQL) or as applications running on the client.
3. Database triggers. Database triggers are PL/SQL programs that are executed when a certain event such as an insert or update of a column occurs on a table. Triggers are usually created only to enforce a complex business rule that cannot be defined by an integrity constraints. Triggers can be enabled or disabled.

In this section, we focus on the integrity constraints.

Creating Constraints

An integrity constraint can be enabled or disabled, validated or non-validated over existing data (for the purpose of constraint violation checking) which are created before the enabling of constraint.

Primary key and unique key constraints are enforced using indexes (creating index is covered in the chapter on managing indexes).  If a constraint is enabled, oracle will create a unique index on the constraint column if the index does not unique, or reuse the existing index.

Foreign key constraint prevents the incorrect manipulation of data references. For example, the DEPARTMENT_ID column in an EMPLOYEE table refers to a parent table DEPARTMENT. A foreign key constraint on DEPARTMENT_ID referring to DEPARTMENT table can prevent us from 1) inserting a new record into EMPLOYEE with a non-existing DEPARTMENT_ID; 2) deleting a record in DEPARTMENT table whose DEPARTMENT_ID value is used in EMPLOYEE table.

Constraints can be created in-line or out-of-line.

• In-line constraints are constraints defined in creating a table.

    For example:

    CREATE TABLE employee (

        id NUMBER(7)  CONSTRAINT employee_id_pk PRIMARY KEY,

        name VARCHAR2(50) CONSTRAINT name_nn NOT NULL,

        dept_id NUMBER(7));

• Out-of-line constraint are defined with ALTER TABLE command.

    For example:

    ALTER TABLE employee

    ADD (CONSTRAINT employee_dept_id_fk FOREIGN KEY (dept_id)

    REFERENCES department (id));

Maintaining Constraints

A constraint that is currently disabled can be disabled in one of the two ways: enable NOVALIDATE or enable VALIDATE.  The former does not check existing data for constraint violation.

The simplified syntax is:

    ALTER TABLE <table name>

    ENABLE NOVALIDATE|VALIDATE {CONSTRAINT <constraint name> | PRIMARY KEY | UNIQUE (column)}

For example, assuming the primary key is not defined for table EMPLOYEE.

    SQL> ALTER TABLE employee ENABLE VALIDATE emp_dept_id_fk;

    SQL> ALTER TABLE employee ENABLE VALIDATE PRIMARY KEY (id);

OR

    SQL> ALTER TABLE employee ENABLE NOVALIDATE emp_dept_id_fk;