Databases Reference
In-Depth Information
and simply include the three contact names in your organization table. This technique
is common in decision-support/data warehousing applications.
Of course, this violation of the rules of normalization limits the flexibility of your ap‐
plication systems—for example, if you later decide that you need four contact names,
some modification of every application and report that uses the contact names will be
necessary. Normalization leads to a more flexible design, which is a good thing in the
constantly changing world we live in.
For this reason, we suggest that you always implement a fully normalized database design
and then, if necessary, go back and denormalize certain tables as needed. With this
approach, you will at least have to make a conscious decision to “break” the normal‐
ization, which involves an active consideration of the price of denormalization.
Constraints
A
constraint
enforces certain aspects of data integrity within a database. When you add
a constraint to a particular column, Oracle automatically ensures that data violating that
constraint is never accepted. If a user attempts to write data that violates a constraint,
Oracle returns an error for the offending SQL statement. Because a constraint is directly
associated with the data it is constraining, the restriction is always enforced, eliminating
the need to implement this integrity restriction in one or more other locations, such as
multiple applications or access tools.
Constraints may be associated with columns when you create or add the table containing
the column (via a number of keywords) or after the table has been created with the SQL
command ALTER TABLE. Since Oracle8, the following constraint types are supported:
NOT NULL
You can designate any column as NOT NULL. If any SQL operation leaves a NULL
value in a column with a NOT NULL constraint, Oracle returns an error for the
statement.
Unique
When you designate a column or set of columns as unique, users cannot add values
that already exist in another row in the table for those columns, or modify existing
values to match other values in the column.
The unique constraint is implemented by a creation of an index, which requires a
unique value. If you include more than one column as part of a unique key, you will
create a single index that will include all the columns in the unique key. If an index
already exists for this purpose, Oracle will automatically use that index.
If a column is unique but allows NULL values, any number of rows can have a NULL
value, because the NULL indicates the absence of a value. To require a truly unique
value for a column in every row, the column should be both unique and NOT NULL.
