A constraint is rule or check or validation defined on the database. Oracle provides two methods of applying validations or business rules on database. First in the form of Constraint and other is Database Trigger. Oracle provides following constraints:
Types of Integrity Constraints
Types of Integrity Constraints
1. Domain Integrity Constarints
These constaints set a range , and any violations that take place will prevent the user from performing the manipulation that caused the breaking the rule.
Types of Domain Integrity Constraints
a) Not Null Constraints
Not Null constraint is enforced though , either on a column or a set of columns in a table , it will not allow Null Value Programmer should mind following rules.
# Not Null integrity constraint can be defined using alter table command even when the table contains rows # Zero and Null are not equivalent # One Null is not not equivalent to another null.
b) Check Constraints
Check constraint can be defined to allow only a particular range of values .
2. Entity Integrity Constarints
An entity is any data recorded in a database . Each row in a table can be uniquely identified using the entity constraint
Types Entity Integrity Constarints
a) Unique Constraints
Usage of the unique key constraint is to prevent the the duplication of values. Column defined with unique constraint can also allow Null values.
b) Primary Key Constraints
The Primary Key constraint avoids duplication of rows and does not allow Null values
3. Referential Integrity Constarints
To establish a ' Parent - Child ' (or) ' Master - Detail ' relationship Between two tables having a common column .
To impliment this constraint should define the column in the parent table as a primary key and the same column in the child table as a foreign key referring to the corresponding parent key .
Basic Concept related to referential integrity are :
a) Foreign Key
A column or combination of columns included in the definition of relation integrity which would refer referenced key.
b) Referenced Key
It is a unique (or) a primary key which is defined on a column belonging to the 41 Child Table.
This table depends upon the values present in the referenced key of the parent table , which is referred by a foreign key .
Parent Table
On Delete Cascade Clause
Deferrable Constraints
To check for constraint violations a setting called defereed constraint can be set. Enabling and Disabling of constraints are also permitted. Programmers must concider the following conditions when would be crossed to the Deferrable constraints.
i) Deferrable intially immediate - this checks for constraint violation at the time of insert.
ii) Deferrable intially deferred - this checks for constraint violation at the time of commit;
iii) Non Defferable intially immediate - this is the default condition which need not be specified .
Enforcing Constraints
Enforcement is applied to any future inserts and updates , but doed not care about data already in the table . This option is very useful when enabling a constraint on a very table since it avoids the considerable effort of checking every row .
Table Level Constraints
Table Level constraint is part of the table definition . An Integrity Constraint defined at table level can force rules on any columns in the table .
Column Level Constraints
Column level constraint being a part of the column definition can be forced only on the column on which it is defined . The ' Not Null ' constraint can be given only at column and not table level.
