Integrity Constraints

Constraints in databases are used to define an integrity constraint, as a rule that restricts the values in a data base.
Now we are discussing 5 types of constraints…
Ø  NOT NULL Constraint
Ø  UNIQUE Constraint
Ø  PRIMARY KEY Constraint
Ø  FOREIGN KEY Constraint
Ø  CHECK Constraint
Declaration Style:
Ø  Column level (OR) IN LINE Style.
Ø  Table level (OR) OUT OF LINE Style.
Column Level:
Ø  They are declared as part of the definition of an individual column or attribute.
Ø  Usually applied when the constraint is specific to that column only.
Table Level:
Ø  They are declared as part of the table definition.
Ø  Definitely applied when the constraint is applied on combination of columns together.
Note:
Ø  NOT NULL Constraint is the only constraint which should be declared as inline only.
Ø  Every constraint is managed by oracle with a constraint name in the Meta data.
Ø  Hence when we declare a constraint if we do not provide a constraint name oracle associates the constraint with name.
Ø  Within a single user no two constraints can have the same name.
Ø  Rather than depending on the oracle supplied constraint name, it is better to define our own name for all constraints.
Ø  When constraints are named we should use ‘CONSTRAINT’ clause.
The CONSTRAINT clause can appear in:
Ø  Create and Alter table statement.
Ø  Create and Alter view statement.
Ø  Oracle does not support constraints on columns or attributes whose data type is
o   USER_DEFINED OBJECTS.
o   NESTED TABLES and VARRAY

o   REF and LOB.