Database Reference
In-Depth Information
What are primary and foreign keys? Going back to Normalization, Nor-
malization separates tables and removes duplicate data values, creating par-
ent and child tables. At the heart of Normal Forms is the creation of unique
primary keys and their related child table foreign keys. A primary key
uniquely identifies a row in a table, namely a parent table. Thus a table can
have only one primary key, identifying each row in that table as being dif-
ferent from every other row in the same table (uniquely). A foreign key is
placed in a child table, being a copy of the primary key value in a related
parent table. The previous section on the evolution of data modeling stated
that the relational data model allows access to any table using key values,
where a table can be linked to any number of other tables. Therefore, a
child table can contain multiple foreign keys and as a result links to multi-
ple parent table primary keys.
So what is Referential Integrity? Referential Integrity is the process of
ensuring the integrity or correctness of data. First, primary key rows in par-
ent tables cannot be deleted unless foreign key child table rows are deleted
first. Second, rows cannot be added to child tables unless foreign key values
exist in parent tables. An exception to this second rule is where a foreign
key value can contain a null value, in the case of a one-to-many-or-zero
relationship. In other words, parent table rows must exist in order for the
child table row to exist but not always.
Referential Integrity is enforced using Oracle constraints, triggers, or
sometimes even at the application level. The most efficient and effective
method of enforcing Referential Integrity in an Oracle database is by
using constraints, which are centralized and only coded or applied once.
Triggers are extremely slow and either generically coded or overcoded.
Application level-coded Referential Integrity can be more difficult to
maintain than triggers.
1.2.2
Denormalization
Denormalization
4
is often, but not always, the opposite of Normalization.
Denormalization should usually be applied to a data model in order to cre-
ate data warehouse or reporting-only type tables. Unfortunately, Denormal-
ization is often required to revive dying applications caused by dreadful
performance. This is often as a result of overzealous use of Normalization in
development of data models and applications. So Denormalization will
often attempt to reverse granularity created by overapplication of Normal
Forms during the Normalization process. Other factors helpful to perfor-
mance can be classified as Denormalization, however vaguely: