In the realm of database design and key classification, there are a total of nine distinct keys to be taken into account.
Primary, foreign, and unique keys are typically well-known among SQL developers, as they are implemented through SQL syntax and serve as constraints. However, when undertaking database design, it is important to take into account additional key types that play a significant role.
The 9 different keys are:
- Primary Key
- Foreign Key
- Unique Key
- Candidate Key
- Secondary/Alternative Key
- Composite Key
- Surrogate Key
- Natural Key
- Super Key
- Minimal Super Key
Here we will briefly describe each one.
- Primary Key: A primary key is a unique identifier for a specific record in a table. It ensures the uniqueness and integrity of the data within that table.
- Foreign Key: A foreign key establishes a relationship between two tables by referencing the primary key of another table. It helps maintain referential integrity and enforces data consistency across related tables.
- Unique Key: A unique key ensures that each value in a specific column or set of columns is unique within a table. Unlike the primary key, a unique key allows for a NULL value.
- Candidate Key: A candidate key is a minimal set of attributes that can uniquely identify a record in a table. Exactly one candidate key will get promoted to a primary key.
- A Secondary or Alternative key is any candidate key not promoted to primary key.
- Composite Key: A composite key consists of multiple columns that together form a unique identifier for a record. The combination of these columns must be unique within the table.
- Surrogate Key: A surrogate key is an artificially generated key, typically an auto-incrementing or unique identifier, used as a substitute for a natural key. It ensures uniqueness and simplifies data management.
- Natural Key: A natural key is a key that has inherent meaning and is derived from the data itself. It is typically based on existing attributes of the entity being represented.
- Super Key: A super key is a set of one or more attributes that can uniquely identify a record in a table. It may contain additional attributes that area not necessary for uniqueness.
- Minimal Super Key: A minimal super key is a super key in which removing any attribute would result in losing the uniqueness property. It contains the minimum number of attributes required to uniquely identify a record. I generally regard minimal super keys as candidate keys,
Hope that helps! If you need more information and examples of these types of keys, a quick internet search will fill in the gaps.