Maintaining Data Integrity
1.Methods to Guarantee Data Integrity
.Application code
.Database triggers
.Declarative integrity constraints
2.Types of Constraints
.NOT NULL
.UNIQUE
.PRIMARY KEY
.FOREIGN KEY
.CHECK
3.Constraint States
An integrity constraint can be enable(data is checked as it is entered or updated) or disabled(data that dose not confirm to the constraint’s rule is prevented from being entered).
An integrity constraint can be in one of the following states:
.DISABLE NOVALIDATE:Old data and new data will not be checked.And the index will be dropped.
.DISABLE VALIDATE:Any modification of the constrained columns is not allowed.In addition,the index on the constraint is dropped and the constraint is disabled.
.ENABLE NOVALIDATE:new data(will be inserted into table) check, old data(original data in the table) not check.If it’s on primary key ,novalid will still check the old data.
Enabling constraints in the novalidated state is most useful in data warehouse configurations that are uploading valid OLTP data.
.ENABLE VALIDATE:Old data and new data will be checked.When a constraint changes to enable validate from disabled state,the table is loced and all data in the table
is checked for soformity.This may cause DML operations such as a data load to wait,so it is advisable to move first from a disabled state
to enable novalidate,and then to enable validate.
Note:Unique or primary key moves from the DISABLE state to ENABLE state and there’s no existing index,a unique index is created automatically.
Similarly,when a unique or primary key moves from ENABLE to DISABLE and it is enabled with a unique index,the unique index is dropped.
Moving a single constraint from ENABLE NOVALIDATE state to ENABLE VALIDATE state dose not block reads,writes or other DDL statements.
4.Constraint Checking
Constraint check road
DML statement->Check nondeferred constraints->COMMIT->Check deefrred constraints
Nodeferred (Immediate) Constraints
Enforced at the end of every DML statement.A constraint that is defined as nondeferrable cannot be modified to be enforced at the end of transaction.
Deferred Constraints
Deferred constraints are constraints that are checked only when a transaction is commited.
A constraint that is defined as deferred can be specified as one of the following
.Initially immediate specifies that by default it should function as an immediate constraint unless explicitly set otherwise.
.Initially deferred specifies that by default the constraint should only be enforced at the end of the transaction
5.Changing the Enforcement of Constraints
.SET CONSTRAINTS statement
1
2
3
4
| ALTER SESSION
SET CONSTRAINT | CONSTRAINT[S]
{constraint |ALL}
{IMMEDIATE|DEFERRED} |
The SET CONSTRAINT mode lasts for duration of the transaction or until another SET CONSTRAINTS statement resets the mode.
The SET CONSTRAINT statement is disllowed inside triggers.
.ALTER SESSION statement
ALTER SESSION
SET CONSTRAINT[S] =
{IMMEDIATE|DEFERRED|DEFAULT}
The ALTER SESSION statement imply setting ALL deferrable constraints statmemt applies to a current session only.
6.Primary and Unique Key Enforcement
Primary and unique keys are enforced using indexes.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
| IF Key_enabled='Yes' THEN
IF Is an index availabe for use?='Yes'
IF Constraint Deferrable?='Yes' THNE
Use existing index;
ELSE
Use existing index;
END IF;
ELSE
IF Constraint deferrable?='Yes' THEN
Create nonunique index;
ELSE
Create unique index;
END IF;
END IF;
ELSE
Do not use index;
END IF |
7.Foreign Key Considerations
--------------------------------------------------------------------------------------------------
Desired Action |Applicaton sulution |
--------------------------------------------------------------------------------------------------
Drop parent table |Cascade constraints |
--------------------------------------------------------------------------------------------------
Truncate parent table |Disable or drop foreign key |
--------------------------------------------------------------------------------------------------
Drop tablespace containing parent table|Use the CASCADE CONSTRAINTS clause |
--------------------------------------------------------------------------------------------------
Perform DML on child table |Ensure the tablespace constaining the parent key is online|
-------------------------------------------------------------------------------------------------- |
.The foreign key must be dropped before dropping the parent table.
DROP TABLE table CASCADE CONSTRAINTS
.The parent table cannot be truncated whithout dropping or disabling the foreign key.
.The foreign must be dropped before tablespace containing the parent is dropped.
1
| DROP TABLESPACE tablespace INCLUDING CONTENTS CASCADE CONSTRAINTS |
If there is no index on the foreign key on the child table, the Oracle server
locks the child table and prevents changes to ensure referential integrity. If there is an index on
the table, the referential integrity is maintained by locking the index entries and avoiding more
restrictive locks on the child table. If both tables need to be updated concurrently from different
transactions, create an index on the foreign key columns.
When data is inserted into or the foreign key column is updated in the child table, the Oracle
server checks the index on the parent table that is used for enforcing the referenced key.
Therefore, the operation succeeds only if the tablespace containing the index is online. Note that
the tablespace containing the parent table does not need to be online to perform DML operations
on the child table.
Oracle9i no longer requires a share lock on unindexed foreign keys when doing an update or
delete on the primary key. It still obtains the table-level share lock, but then releases it
immediately after obtaining it. If multiple primary keys are update or deleted, the lock is obtained
and released once per row.
8.Defining Constraings While Creating a Table
Syntax
1
2
3
4
5
6
7
8
9
10
11
12
13
| column datatype [CONSTRAINT constraint]
{[NOT] NULL
|UNIQUE [USING INDEX index_clause]
|PRIMARY KEY [USING INDEX index_clause]
|REFERENCES [schema.]table [(column)]
[ON DELETE CASCADE]
|CHECK (condition)
}
constraint_state :==
[NOT DEFERRABLE|DEFERRABLE [INITIALLY
{IMMEDIATE|DEFERRED}]
]
[DISABLE|ENABLE [VALIDATE|NOVALIDATE]] |
Defining a constraint from the type NOT NULL after creating a table is only possible with:
1
| ALTER TABLE table_name MODIFY col_name CONSTRAINT constraint_name NOT NULL; |
9.Defining Constraints After Creating a Table:Example
1
2
3
4
| ALTER TABLE hr.employee
ADD (CONSTRAINT employee_dept_id_fk FOREIGN KEY(dept_id)
REFERENCES hr.department(id)
DEFERRABLE INITIALLY DEFERRED); |
10.Guidelines for Defining Constraints
. Place indexes used for enforcing primary key and unique constraints in a tablespace different from that of the table.
. If data is frequently loaded in bulk into a table, it is preferable to disable the constraints,
perform the load, and then enable the constraints. If a unique index is used for enforcing a
primary key or unique constraint, this index needs to be dropped when the constraint is
disabled. Performance can be enhanced by using a nonunique index for enforcement of
primary key or unique constraints in such situations: either create the key as deferrable or
create the index before defining or enabling the key.
. If a table contains a self-referencing foreign key, use one of the following methods to load
data:
– Define or enable the foreign key after the initial load
– Define the constraint as a deferrable constraint
The second method is useful if data loads are performed frequently.
11.Using the EXCEPTIONS Table
.Create exception table use:$ORACLE_HOME/rdbms/admin/utlexcpt.sql
.Add constraint with exceptions into clause
eg:
1
2
| ALTER TABLE table_name ADD CONSTRAINT constraint_name
UNIQUE(col_name); |
You also can use the following statement to except the duplicated rows after data load.
1
2
3
| ALTER TABLE table_name
ENABLE VALIDATE CONSTRAINT constraint_name
EXCEPTIONS INTO schema.exceptions; |
12.Obtaining Constraint information
.DBA_CONSTRAINTS
.DBA_CONS_COLUMNS
Recent Comments