Primary Key and Its Key Characteristics
Arguably, the most important characteristic of an entity is its primary key (a single attribute or some combination of attributes), which uniquely identifies each entity instance. The primary key’s function is to guarantee entity integrity. Furthermore, primary keys and foreign keys work together to implement relationships in the relational model.
Natural Keys and Primary Keys:
A natural key or natural identifier is a real-world, generally accepted identifier used to distinguish—that is, uniquely identify—real-world objects. As its name implies, a natural key is familiar to end users and forms part of their day-to-ay business vocabulary. Usually, if an entity has a natural identifier, a data modeler uses that as the primary key of the entity being modeled. Generally, most natural keys make acceptable primary key identifiers.
Primary Key Guidelines:
A primary key is the attribute or combination of attributes that uniquely identifies entity instances in an entity set.
The guidelines for selecting primary key
• First, you should understand the function of a primary key. The primary key’s main function is to uniquely identify an entity instance or row within a table. In particular, given a primary key value—that is, the determinant—the relational model can determine the value of all dependent attributes that “describe” the entity. Note that identification and description are separate semantic constructs in the model. The function of the primary key is to guarantee entity integrity, not to “describe” the entity.
• Second, primary keys and foreign keys are used to implement relationships among entities. However, the implementation of such relationships is done mostly behind the scenes, hidden from end users. In the real world, end users identify objects based on the characteristics they know about the objects.
The desirable primary key characteristics are as follows:
1. Unique values: The PK must uniquely identify each entity instance. A primary key must be able to guarantee unique values. It cannot contain nulls.
2. Non intelligent: The PK should not have embedded semantic meaning other than to uniquely identify each entity instance. An attribute with embedded semantic meaning is probably better used as a descriptive characteristic of the entity than as an identifier. For example, a student ID of 650973 would be preferred over Smith, Martha L. as a primary key identifier.
3. No change over time: If an attribute has semantic meaning, it might be subject to updates. This is why names do not make good primary keys. If you have Vickie Smith as the primary key, what happens if she changes her name when she gets married? If a primary key is subject to change, the foreign key values must be updated, thus adding to the database work load. Furthermore, changing a primary key value means that you are basically changing the identity of an entity. In short, the PK should be permanent and unchangeable.
4. Preferably single-attribute: A primary key should have the minimum number of attributes possible (irreducible).Single-attribute primary keys are desirable but not required. Single attribute primary keys simplify the implementation of foreign keys. Having multiple-attribute primary keys can cause primary keys of related entities to grow through the possible addition of many attributes, thus adding to the database work load and making (application) coding more cumbersome.
5. Preferably Numeric: Unique values can be better managed when they are numeric, because the database can use internal routines to implement a counter-style attribute that automatically increments values with the addition of each new row. In fact, most database systems include the ability to use special constructs, such as Autonumber in Microsoft Access, to support self-incrementing primary key attributes.
6. Security-Compliant: The selected primary key must not be composed of any attribute(s) that might be considered a security risk or violation. For example, using a Social Security number as a PK in an EMPLOYEE table is not a good idea.
Difference between partial completeness and total completeness
