Constraints
An Oracle constraint is a rule or rules that you can define on one or more columns in a table
to help enforce a business rule. For example, a constraint can enforce the business rule that an
employee??™s starting salary must be at least $25,000.00. Another example of a constraint enforcing
a business rule is to require that if a new employee is assigned a department (although they need
not be assigned to a particular department right away), the department number must be valid and
exist in the DEPT table.
Six types of data integrity rules can be applied to table columns: null rule, unique column
values, primary key values, referential integrity values, complex in-line integrity, and trigger-based
integrity. We will touch upon each of these briefly in the following sections.
All the constraints on a table are defined either when the table is created or when the table is
altered at the column level, except for triggers, which are defined according to which DML operation
you are performing on the table. Constraints may be enabled or disabled at creation or at any
point of time in the future; when a constraint is either enabled or disabled (using the keyword
enable or disable), existing data in the table may or may not have to be validated (using the
keyword validate or novalidate) against the constraint, depending on the business rules in effect.
16 Oracle Database 11g DBA Handbook
For example, a table in an automaker??™s database named CAR_INFO containing new automobile
data needs a new constraint on the AIRBAG_QTY column, where the value of this column must
not be NULL and must have a value that is at least 1 for all new vehicles.
Pages:
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77