Key or Identifier, is a set of one or more attributes that uniquely defines an instance of an entity.
Keys by Construction-Type
- Surrogate Key is a unique identifier for a table. Counter and System-Generated without Intelligence. Surrogate Key is an integer whose meaning is unrelated to its face value. Let’s say Month Identifier of 3 cannot be assumed to represent March. Surrogate keys serve technical functions and must not be visible to end users. A surrogate key is a unique identifier for a table.
- Simple Key is an attribute that uniquely identifies an entity instance. Surrogate Key is also an example of a simple key.
- Composite Key contains one compound key and at least one other simple or compound key or non-key attribute.
- Compound Key is a set of two or more attributes that together uniquely identify an entity instance.
Keys by Function-Type
- Primary Key is the candidate key that is chosen to be the unique identifier for an entity. Even though an entity may contain more than one candidate key, only one candidate key can serve as the primary key for an entity.
- Alternate Key is a candidate key that although unique, was not chosen as the primary key. An alternate key can still be used to find specific entity instances. Often the primary key is a surrogate key and the alternate keys are business keys.
- Super Key is any set of attributes that uniquely identify an entity instance.
- Foreign Key is used in physical and sometimes logical relational Data Modeling schemes to represent a relationship. Foreign Key may be created implicitly when a relationship is defined between two entities, depending on the database technology or data modeling tool, and whether the two entities involved have mutual dependencies.
- Business Key is one or more attributes that a business professional would use to retrieve a single entity instance. Candidate keys can be Business Keys, sometimes also called Natural Keys. Business Keys and Surrogate Keys are mutually exclusive.
