Database Reference
In-Depth Information
A key is one or more columns of a relation that is used to identify a row.
Primary Keys
A primary key is an attribute (column) or combination of attributes (columns) whose values uniquely
identify records in an entity.
Before you choose a primary key for an entity, an attribute must have the following properties:
•
Each record of the entity must have a not-null value.
•
The value must be unique for each record entered into the entity.
•
The values must not change or become null during the life of each entity instance.
•
There can be only one primary key defined for an entity.
Besides helping in uniquely identifying a record, the primary key also helps in searching records
because an index automatically gets generated as you assign a primary key to an attribute.
An entity will have more than one attribute that can serve as a primary key. Any key or minimum set
of keys that could be a primary key is called a
candidate key.
Once candidate keys are identified, choose
one, and only one, primary key for each entity.
Sometimes it requires more than one attribute to uniquely identify an entity. A
primary key
that
consists of more than one attribute is known as a
composite key.
There can be only one
primary key
in an
entity, but a
composite key
can have multiple attributes (in other words, a
primary key
will be defined
only once, but it can have up to 16 attributes). The primary key represents the parent entity. Primary
keys are usually defined with the
IDENTITY
property, which allows insertion of an auto-incremented
integer value into the table when you insert a row into the table.
When an extra attribute is an identity property and it is added to a column, it is called as
surrogate
key
. The value of such columns is generated at runtime right before the record is inserted and then
stored into a table.
Foreign Keys
A
foreign key
is an attribute that completes a relationship by identifying the parent entity. Foreign keys
provide a method for maintaining integrity in the data (called
referential integrity)
and for navigating
between different instances of an entity. Every relationship in the model must be supported by a foreign
key. For example, in Figure 2-1 earlier, the Customers and Orders tables have a primary key and foreign
key relationship, where the Orders table's CustomerID field is the foreign key having a reference to the
CustomerID field, which is the primary key of the Customers table.
Understanding Data Integrity
Data integrity
means that data values in a database are correct and consistent. There are two aspects to
data integrity:
entity integrity
and
referential integrity
.
Entity Integrity
We mentioned previously in “Primary Keys” that no part of a primary key can be null. This is to
guarantee that primary key values exist for all rows. The requirement that primary key values exist and
that they are unique is known as
entity integrity
(EI). The DBMS enforces
entity integrity
by not allowing
operations (
INSERT
,
UPDATE
) to produce an invalid primary key. Any operation that creates a duplicate
