2.4 IDENTIFYING KEYS
2.4.1 Primary Key and Entity Integrity
An attribute that uniquely identifies one and only one instance of an entity is called a primary key. Sometimes a primary key consists of a combination of attributes. It is referred to as a composite key. The entity integrity rule states that no attribute that is a member of the primary (composite) keymay accept a null value.
A faculty _ id may serve as a primary key for the Faculty entity, assuming that all faculty members have been assigned a unique FaultyID. However, caution must be exercised when picking an attribute as a primary key. Last Name may not make a good primary key because a department is likely to have more than one person with the same last name. Primary keys for the CSE _ DEPT database are shown in Table 2.6.
TABLE 2.6
Faculty Table
Primary keys provide a tuple-level addressing mechanism in the relational database. Once you define an attribute as a primary key for an entity, the DBMS will enforce the uniqueness of the pri-mary key. Inserting a duplicate value for primary key field will fail.
2.4.2 Candidate Key
There can be more than one attribute that uniquely identifies an instance of an entity. These are referred to as candidate keys. Any one of them can serve as a primary key. For example, ID Number as well as Social Security Number may make a suiTable primary key. Candidate keys that are not used as primary key are called alternate keys.