Types of Database Keys

Understanding database keys is essential for managing data effectively. These keys help maintain data integrity, establish relationships between tables, and ensure unique identification of records. Each type of key plays a specific role in organizing and retrieving data efficiently.

1. Primary Key

   • Uniquely identifies each record in a database table.
   • Cannot contain NULL values; every record must have a value.
   • Ensures data integrity and prevents duplicate entries.

2. Foreign Key

   • A field (or collection of fields) in one table that refers to the primary key in another table.
   • Establishes a relationship between two tables, enabling data normalization.
   • Can contain NULL values, allowing for optional relationships.

3. Composite Key

   • A key that consists of two or more columns used together to uniquely identify a record.
   • Useful when a single column is not sufficient to ensure uniqueness.
   • Often used in junction tables to represent many-to-many relationships.

4. Candidate Key

   • A set of one or more columns that can uniquely identify a record in a table.
   • There can be multiple candidate keys in a table, but only one can be chosen as the primary key.
   • Must be unique and not contain NULL values.

5. Super Key

   • A superset of candidate keys that can uniquely identify records in a table.
   • May include additional attributes that are not necessary for uniqueness.
   • Helps in identifying all possible keys for a table.

6. Alternate Key

   • A candidate key that is not selected as the primary key.
   • Still maintains uniqueness and can be used to access records.
   • Provides additional ways to retrieve data without relying solely on the primary key.

7. Unique Key

   • A constraint that ensures all values in a column are distinct from one another.
   • Unlike primary keys, unique keys can accept NULL values (but only one NULL per column).
   • Helps maintain data integrity by preventing duplicate entries.

8. Natural Key

   • A key that is derived from the actual data and has a logical relationship to the data it represents.
   • Often used in real-world scenarios, such as Social Security numbers or email addresses.
   • Can be subject to change, which may affect data integrity.

9. Surrogate Key

   • An artificially created key, often a sequential number, used to uniquely identify records.
   • Does not have any business meaning and is typically generated by the database system.
   • Provides stability as it is not affected by changes in the actual data.

10. Secondary Key

    • A key that is used for data retrieval but does not uniquely identify records.
    • Often used to create indexes for faster search operations.
    • Can be based on one or more columns and may allow duplicate values.