Everything about "Keys" in Relational Databases

In Oracle databases, keys are essential for maintaining data integrity, enforcing relationships, and ensuring uniqueness in tables. Below is a detailed explanation of all types of keys and related terms in Oracle databases:

---

1. Primary Key

• Definition: A primary key is a column or set of columns that uniquely identifies each row in a table.

• Characteristics:

  • Must contain unique values (no duplicates).

  • Cannot contain NULL values.

  • A table can have only one primary key.

• Purpose: Ensures entity integrity by uniquely identifying each record.

• Example:

    CREATE TABLE employees (
        employee_id NUMBER PRIMARY KEY,
        first_name VARCHAR2(50),
        last_name VARCHAR2(50)
    );
  

---

2. Unique Key

• Definition: A unique key is a column or set of columns that ensures all values are unique across the table.

• Characteristics:

  • Allows NULL values (unlike a primary key).

  • A table can have multiple unique keys.

• Purpose: Ensures uniqueness but does not necessarily identify a row uniquely.

• Example:

    CREATE TABLE employees (
        employee_id NUMBER PRIMARY KEY,
        email VARCHAR2(100) UNIQUE
    );
  

---

3. Foreign Key

• Definition: A foreign key is a column or set of columns in one table that references the primary key or unique key in another table.

• Characteristics:

  • Establishes a relationship between two tables.

  • Ensures referential integrity by enforcing valid references.

  • Can contain NULL values.

• Purpose: Maintains relationships between tables and prevents orphaned records.

• Example:

    CREATE TABLE orders (
        order_id NUMBER PRIMARY KEY,
        employee_id NUMBER,
        FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
    );
  

---

4. Composite Key

• Definition: A composite key is a primary key or unique key that consists of two or more columns.

• Characteristics:

  • Used when a single column cannot uniquely identify a row.

  • Combines multiple columns to ensure uniqueness.

• Purpose: Provides a way to uniquely identify rows when a single column is insufficient.

• Example:

    CREATE TABLE order_items (
        order_id NUMBER,
        product_id NUMBER,
        quantity NUMBER,
        PRIMARY KEY (order_id, product_id)
    );
  

---

5. Superkey

• Definition: A superkey is a set of one or more columns that can uniquely identify a row in a table.

• Characteristics:

  • May include additional columns beyond what is necessary for uniqueness.

  • A candidate key is a minimal superkey (no redundant columns).

• Purpose: Used in database design to identify candidate keys and primary keys.

• Example:

  • In a table with columns employee_id, email, and phone, the combination of employee_id and email is a superkey.

---

6. Candidate Key

• Definition: A candidate key is a minimal superkey that can uniquely identify a row in a table.

• Characteristics:

  • Must be unique and non-redundant.

  • A table can have multiple candidate keys.

  • One of the candidate keys is chosen as the primary key.

• Purpose: Used to determine the best primary key for a table.

• Example:

  • In a table with columns employee_id and email, both employee_id and email are candidate keys.

---

7. Alternate Key

• Definition: An alternate key is a candidate key that is not chosen as the primary key.

• Characteristics:

  • Still enforces uniqueness.

  • Can be used as a backup or secondary unique identifier.

• Purpose: Provides additional unique identification options.

• Example:

  • If employee_id is the primary key, email can be an alternate key.

---

8. Surrogate Key

• Definition: A surrogate key is an artificial or system-generated key (e.g., a sequence or auto-incremented number) used to uniquely identify a row.

• Characteristics:

  • Has no business meaning.

  • Often used in data warehousing or when natural keys are not suitable.

• Purpose: Simplifies key management and ensures uniqueness.

• Example:

    CREATE TABLE employees (
        employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
        first_name VARCHAR2(50),
        last_name VARCHAR2(50)
    );
  

---

9. Natural Key

• Definition: A natural key is a column or set of columns that have inherent business meaning and can uniquely identify a row.

• Characteristics:

  • Derived from real-world data (e.g., email, social_security_number).

  • May change over time, which can complicate database design.

• Purpose: Provides meaningful identification of rows.

• Example:

  • Using email as a natural key in a users table.

---

10. Compound Key

• Definition: A compound key is another term for a composite key, where two or more columns are used together to form a primary or unique key.

• Example:

    CREATE TABLE order_items (
        order_id NUMBER,
        product_id NUMBER,
        PRIMARY KEY (order_id, product_id)
    );
  

---

11. Partial Key

• Definition: A partial key is a key that is part of a composite key but does not uniquely identify a row on its own.

• Characteristics:

  • Used in weak entities (entities that depend on another entity for existence).

• Purpose: Helps identify rows in conjunction with another key.

• Example:

  • In a dependents table, dependent_id might be a partial key, combined with employee_id to form a composite key.

---

12. Superkey vs. Candidate Key

• Superkey: Any set of columns that can uniquely identify a row (may include extra columns).

• Candidate Key: A minimal superkey (no redundant columns).

---

13. Key Constraints

• Primary Key Constraint: Enforces uniqueness and non-null values for the primary key.

• Unique Constraint: Ensures uniqueness for a unique key.

• Foreign Key Constraint: Enforces referential integrity between tables.

---

14. Key-Related Terms

• Index: A database object used to speed up data retrieval. Often created automatically for primary keys and unique keys.

• Sequence: A database object used to generate unique numbers, often for surrogate keys.

• Constraint: A rule enforced by the database to maintain data integrity (e.g., primary key constraint, foreign key constraint).

---

Summary of Key Types

---

This comprehensive overview covers all types of keys and related terms in Oracle databases. Let me know if you need further clarification or examples!