Chapter 8 : MANAGING DATA INTEGRITY

Chapter 8 : MANAGING DATA INTEGRITY

CHAPTER # 8

MANAGING DATA INTEGRITY

DATA INTEGRITY:

Data integrity means data in a database adheres (kept) to business rules.

There are three primary rules in which data integrity can be maintained:

• Application code
• Database triggers
• Declarative integrity constraints

The DBA is primary concerned with implementing the methods chosen by the designer and balancing the performance needs against integrity requirements.

APPLICATION CODE:

Application code can be implemented either as stored procedures within the database or as application running in the client.

DATABASE TRIGGERS:

Database triggers are PL/SQL programs that are executed when an event, such as an insert or update of a column, occurs on a table. Triggers can be enabled or disabled. Database triggers are usually created only to enforce a complex business rule that cannot be defined as an integrity constraint.

INTEGRITY CONSTRAINTS:

Integrity constraints are the preferred mechanism for enforcing business rules because they:

• Provide improved performance
• Are easy to declare and modify – they do not require execute coding
• Centralize rules
• Are flexible (enabled or disabled)
• Are fully documented in the data dictionary.

The following sections explain the behavior of integrity constraints and discuss how they are implemented by the oracle server.

Types Of Constraints:

NOT NULL:

By default all column in a table allow null value. Null means absence of value. A not null constraint requires a table column to must contain value.

NOTE:    Not null constraint defines only COLUMN LEVEL.

UNIQUE:

A unique key constraint requires that every value in a column or set of column (key) be unique. Means no duplicate value allows but null value accepted.

PRIMARY KEY:

Each table in the database can contain at most one PRIMARY KEY constraint. Primary key constraints ensure that both of the following are true:

• No duplicate value allow
• Do not contain NULL value

FOREIGN KEY:

Foreign key constraint designates a column or combination of column use as a foreign key and establishes a relationship b/w a primary key or unique key in the same table or different table.

NOTE: Foreign key constraint can be defined at the column level or table level.

            A composite foreign key must be created by using the table level definations.

CHECK CONSTRAINT:

The check constraint on a column or a set of column requires that a specified condition be true or unknown for every row of the table.

A single column can have multiple check constraint.

There is no limit to the number of check constraint which you can define on a column.

NOTE: check constraint can be defines as column level or table level.

Although NOT NULL and CHECK constraint do not directly requires DBA attention.

PRIMARY KEY, UNIQUE KEY, FOREIGN KEY constraint must be managed by DBA to ensure high availability and acceptable performance.

CONSTRAINTS STATES:

An integrity constraint can be one of the following states:

1.      DISABLE NOVALIDATE (DEFAULT)

2.      DISABLE VALIDATE

3.      ENABLE NOVALIDATE

4.      ENABLE VALIDATE       (DEFAULT)

DISABLE NOVALIDATE (DEFAULT)

A constraint that is DISABLE NOVALIDATE is not checked.

Data in the table, as well as new data that is entered or updated, may not conform to the rules defined by the constraint.

DISABLE VALIDATE

If a constraint in this state that any modification of the constraint column is not allowed (means DML locked). In addition the index on the constraint is dropped and the constraint is Disable.

NOTE: The index is not dropped if the constraint is deferrable.

ENABLE VALIDATE:

If the constraint in this state, a row that violates the constraint cannot be inserted into the table. How ever such a row can be inserted while the constraint is disable. This row is known as exception to the table.

When a constraint changes to ENABLE VALIDATE from a DISABLED state, the table is locked and all the data in the table is check conformity (all data means old+new inserted data). This may cause DML operation such as a data load to wait. So it is advisable to move first from disable state to enable novalidate and that enable validate.

ENABLE NOVALIDATE:

CONSTRAINT CHECKING:

NONDEFFERED OR IMMEDIATE CONSTRAINTS:

NONDEFERRED constraints, also known as immediate constraints, are enforced every DML statement.

A constraint violation causes the statement to be rollback

By default rollback is set.

DEFERRED CONSTRAINT:

Deferred constraints are the constraints that are checked only when a transaction is committed.

If any constraint violation is detected at commit time, the entire transaction is rollback.  

A constraint that is defined as deferrable can be specified as one of the following.

• INITIALLY IMMEDIATE: (Default)

Specifies that by default it should function as a immediate constraint unless explicitly set otherwise.

• INITIALLY DEFERRED:

Specifies that by default the constraint should be enforced only at the end of the transaction.

DEFINING CONSTRAINTS

IMMEDIATE OR DEFERRED

ALTER SESSION SET CONSTRAINT = {IMMEDIATE | DEFERRED | DEFAULT}

OR

SET CONSTRAINT {CONSTRAINT NAME} {IMMEDIATE | DEFFERED}

PRIMARY KEY AND UNIQUE KEY ENFORCEMENT:

If the constraint is disable, no index are needed

Constraint Disable  à No index

If the constraint is enable, and the index is not available for use, and constraint is not deferrable, than create unique index

Constraint Enable à No Index Available à No deferrable constraint àcreate unique index.

If the constraint is enable, and the index is not available for use, and constraint is  deferrable, than create nonunique index

Constraint Enable à No Index Available à Deferrable constraint à create nonunique index.

If the constraint is enable, and the index available for use, and constraint is not deferrable, than use existing index

Constraint Enable à Index Available à No Deferrable Constrant à Use Existing Index.

If the constraint is enable, and the index available for use, and constraint is deferrable, if the index unique / nonunique, than use existing index

Constraint Enable à Index Available à Deferrable Constraint à Use Existing Index.

FOREIGN KEY CONSIDRATION:

You should consider several factors when you maintain tables that are in a foreign key relationship.

When you dropped parent table first you must drop foreign key.

Either single statement can be used to perform both actions:

SQL > DROP TABLE EMP CASCADE CONSTRAINTS;

The parent table cannot be truncated without dropping or disabling the foreign key.

When you dropped tablespace containing the parent table first you must drop foreign key

Either you can achieve this by using the following command:

SQL > DROP TABLESPACE USERS INCLUDING CONTENTS

            CASCADE CONSTRAINTS;

When perform DML on child table first ensure that the tablespace containing the parent key is online.

If delete cascade option is not used when rows are deleted from the parent table the oracle server must ensure that there is no row available in child table.

Similarly, an update to the parent key is permitted only when there are no child rows with the old key value. 

NOTE: The tablespace that contain the parent table does not need to be online to perform DML operation on child table.

DEFINING CONSTRAINT WHILE CREATING A TABLE

PRACTICAL #

SQL> CREATE USER ABC IDENTIFIED BY ABC;

User created.

SQL> GRANT CONNECT, RESOURCE TO ABC;

Grant succeeded.

SQL> DESC DBA_TABLESPACES;

SQL> SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;

SQL> ALTER USER ABC DEFAULT TABLESPACE ABC;

User altered.

SQL> CONN ABC/ABC

Connected.

SQL> CREATE TABLE EMP

  2  (ID NUMBER(04) CONSTRAINT EMP_ID_PK PRIMARY KEY

  3  DEFERRABLE

  4  USING INDEX

  5  STORAGE (INITIAL 100K NEXT 100K)

  6  TABLESPACE USERS,

  7  LAST_NAME VARCHAR2(20) CONSTRAINT EMP_LNAME_NN NOT NULL,

  8  DEPT_ID NUMBER(04));

Table created.

SQL> DROP TABLE EMP;

Table dropped.

SQL>  CREATE TABLE EMP

  2  (ID NUMBER(04) CONSTRAINT EMP_ID_PK PRIMARY KEY

  3  DEFERRABLE

  4  USING INDEX

  5  STORAGE (INITIAL 100K NEXT 100K)

  6  TABLESPACE INDX,

  7  LAST_NAME VARCHAR2(20) CONSTRAINT EMP_LNAME_NN NOT NULL,

  8  DEPT_ID NUMBER(04))

  9* TABLESPACE USERS

SQL> /

Table created.

(Index and table are kept separately for better performance)

A constraint can be defined either when creating a table

or

Defined after table creation using ALTER TABLE statements

PRACTICAL #

SQL> CREATE TABLE EMP1

 

  2  (ID NUMBER(04),

  3  NAME VARCHAR2(20),

  4  SAL NUMBER(5),

  5  JOB VARCHAR2(20),

  6* DEPTNO NUMBER(2))

SQL> /

TABLE CREATED.

SQL> ALTER TABLE EMP1

  2* ADD (CONSTRAINT EMP1_ID_PK PRIMARY KEY(ID) DEFERRABLE INITIALL

SQL> /

TABLE ALTERED.

SQL> ALTER TABLE EMP1

  2* ADD (CONSTRAINT EMP1_NAME_UNIQUE UNIQUE (NAME) )

SQL> /

TABLE ALTERED. 

NOTE: Not null constraint cannot be added after table creation.

It is possible only with

SQL > ALTER TABLE EMP1 MODIFY DEPTNO CONSTRAINT emp1_deptno_NN

            NOT NULL;

GUIDELINES FOR DEFINING CONSTRAINT:

Primary and unique constraints:

• Place indexes in a separate tablespace.

You can do this by specifying the USING INDEX clause by creating the table, or creating the separate index, and altering the table to add or enable the constraint.

• Use nonunique indexes if bulkload frequent.

If data is frequently load in bulk into a table, it is preferable to disable constraints, perform load and then enable the constraint.

If a unique index is used to enforce a primary key or unique key constraint.then this index must be dropped when the constraint is disabled.

Performance can be enhanced by using a nonunique index.

Self-Referencing foreign keys:

• Define or enable foreign keys after the initial load.
• Defer constraint checking

ENABLING CONSTRAINTS:

A constraint that is currently disabled can be enabled in one of the two ways:

Enable novalidate

Enable validate

ENABLE NOVALIDATE:

·         No locks on tables

·         Primary and unique keys must use nonnuique indexes.

For primary key and unique key constraints that have an existing index, enabling a NOVALIDATE constraint is much faster than VALIDATE constraint existing data is not checked for constraint violation if the constraint is deferrable. If this option is used for enabling the constraint, no lock is required on the table.

This method is appropriate where there is a lot of DML activity on a table, in case of OLTP environment.

PRACTICAL #

First constraint disable of any column

SQL> alter table abc.emp1

  2  disable constraint emp1_name_unique;

Table altered.

Then enabled by

SQL> alter table abc.emp1

enable novalidate constraint emp1_id_pk;

Table altered.

ENABLE VALIDATE:

Enabling a validate constraint checks existing data for constraint violation. This is the default when a constraint is enabled

If executed when the constraint is disabled, it has the following effects:

• Locks the table

The table is locked and changes to the table are prevented until validation of existing data is complete.

The oracle server creates an index if one does not exist on the index columns. It creates the unique index while enabling a primary key or unique key constraint that is nondeferrable. 

• Can use unique and nonunique indexes
• Needs valid table data.

SQL> alter table abc.emp1

enable validate constraint emp1_id_pk;

Table altered.

NOTE:

• The VALIDATE option is the default and does not need to be specified when enabling a constraint that is disabled.
• If the data in the table violates the constraint. Then the statement is rolled back and the constraint remains disabled.
• The use of EXCEPTIONS clause is discussed in the following section.

RENAMING CONSTRAINTS

Use the following command to rename a constraint:

PRACTICAL #

ALTER TABLE ABC.EMP1

RENAME CONSTRAINT emp1_id_pk

TO employee_id_pk;

USING THE EXCEPTION TABLE

The EXCEPTIONS clause identifies any row that violates an enabled constraint.

Use the following procedure to detect constraint violations, rectify then, and re-enable a constraint.

PRACTICAL #

STEP # 1

Run the  utlexpt1.sql script

Path:  @D:\oracle\orcl\rdbms\admin\utlexpt1

SQL > Desc exceptions;

STEP # 2:

SQL> create user abc identified by abc ;

User created.

SQL> grant connect, resource to abc;

Grant succeeded.

SQL> CONN ABC/ABC

Connected.

STEP # 3:

SQL> create table emp1

  2  (empid number(04) constraint emp1_empid_pk primary key ,

  3   ename varchar2(20) constraint emp1_ename_nnn not null ,

  4   deptno number(04));

Table created.

STEP # 4:

Insert unique value in emp1 table because pk in empid column is enabled

SQL>  insert into emp1

  2   values

  3   (11, 'raheel' ,10);

1 row created.

SQL>  insert into emp1

  2   values

  3   (12, 'rehman' ,10);

1 row created.

SQL>  insert into emp1

  2   values

  3   (13, 'khan' ,10);

1 row created.

SQL>  insert into emp1

  2   values

  3   (14, 'zaib' ,20);

1 row created.

SQL>  insert into emp1

  2   values

  3   (14, 'jazzy' ,30);

1 row created.

STEP # 5:

Now conn SYS and disable constraint PK of impid

SQL> conn sys/oracle as sysdba

Connected.

SQL> alter table abc.emp1

  2  disable constraint emp1_empid_pk;

Table altered.

STEP # 6:

Now insert again in emp1 table duplicate value allow because constraint disable.

SQL>  insert into emp1

  2   values

  3   (11, 'zafar', 70);

1 row created.

SQL>  insert into emp1

  2   values

  3   (15, 'shahid', 70);

1 row created.

SQL>  insert into emp1

  2   values

  3   (15, 'kashif', 70);

1 row created.

STEP # 7:

Now conn SYS and truncate exceptions table

SQL> conn sys/oracle as sysdba

Connected.

SQL> truncate table exceptions;

Table truncated.

STEP # 8:

Now enable constraint there is an error generate

SQL>   alter table abc.emp1

  2  enable validate constraint emp1_empid_pk

  3* exceptions into sys.exceptions;

alter table abc.emp1

*

ERROR at line 1:

ORA-02437: cannot validate (ABC.EMP1_EMPID_PK) - primary key violated

STEP # 9:

Identify invalid data by using a subquery on the EXCEPTIONS table

SQL> select rowid, empid, ename, deptno

  2  from abc.emp1

  3  where rowid in (select row_id from exceptions)

  4  for update;

STEP # 10:

SQL> update abc.emp1

  2  set empid=16

  3  where rowid='AAAHZ5AABAAAMV6AAI';

1 row updated.

SQL> update abc.emp1

  2  set empid=17

  3  where rowid='AAAHZ5AABAAAMV6AAH';

1 row updated.

SQL> update abc.emp1

  2  set empid=18

  3  where rowid='AAAHZ5AABAAAMV6AAH';

1 row updated.

STEP # 11:

Truncate the exception table

SQL> truncate table exception;

Table truncated.

STEP # 12:

Reanbale constraint

SQL> alter table abc.emp1

  2  enable validate constraint emp1_empid_pk

  3* exceptions into sys.exceptions;

Table altered.

OBTAINING CONSTRAINT INFORMATION:

SQL > DESC DBA_CONSTRAINTS

SQL > DESC DBA_CONS_COLUMNS

--------------------------------  END OF CHAPTER 8  -----------------------------