In the realm of database design, there are a total of twelve 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 consider additional key types that play a significant role.
The 12 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
- Compound Key
- Cluster 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.
- Secondary or Alternative Key is any candidate key not promoted to the 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 are 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.
- Compound Key: A compound key is a kind of composite key where each column is also a primary key in another table.
- Cluster Key: A cluster key is a column or set of columns that defines the order in which data rows are stored in a table when a clustered index is applied. Unlike a non-clustered index, which maintains a separate data structure to hold the index data and a pointer to the actual data rows, a clustered index sorts and stores the data rows in the table based on the cluster key.
Also, I should point out that you may also hear the terms business key, join key, grouping key, etc. being used. These terms often relate to an attribute or set of attributes on a table that is used to group on, join, pivot, etc. Business keys and join keys are informal terms that the database community does not have a commonly accepted term, and you need to understand the context in which they are used.
Hope that helps! If you need more information and examples of these types of keys, a quick internet search will fill in the gaps.
Happy coding!