SQL Keys

In SQL, keys are attributes or a set of attributes used to uniquely identify rows in a table, establish relationships between tables, and enforce data integrity. Below are the types of keys commonly used in SQL:

---

1. Primary Key

• Definition: Uniquely identifies each row in a table.
• Characteristics:
  • Cannot have NULL values.
  • Must contain unique values.
  • A table can have only one primary key.
• Result: employee_id ensures no two rows have the same value.

CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(100) );

---

2. Foreign Key

• Definition: Establishes a relationship between two tables by referencing the primary key of another table.
• Characteristics:
  • Enforces referential integrity.
  • Allows NULL if not explicitly restricted.
• Result: Ensures that customer_id in orders matches an existing customer_id in the customers table.

CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); 

---

3. Unique Key

• Definition: Ensures all values in the column or combination of columns are unique.
• Characteristics:
  • Similar to the primary key but allows one NULL value.
  • A table can have multiple unique keys.
• Result: Ensures sku values are unique across rows.

CREATE TABLE products ( product_id INT PRIMARY KEY, sku VARCHAR(50) UNIQUE );

---

4. Candidate Key

• Definition: A column or set of columns that can qualify as a unique identifier for rows in a table.
• Characteristics:
  • A table can have multiple candidate keys.
  • One of the candidate keys becomes the primary key.

-- In a table with `employee_id` and `email`, both can be candidate keys

---

5. Composite Key

• Definition: A primary key that consists of two or more columns.
• Characteristics:
  • Used when a single column cannot uniquely identify a row.
• Result: Combines student_id and course_id to uniquely identify rows.

CREATE TABLE enrollment ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id) ); 

---

6. Alternate Key

• Definition: A candidate key that is not selected as the primary key.

-- In a table, if both `email` and `user_id` are unique, one is the primary key, and the other is an alternate key.

---

7. Super Key

• Definition: A set of one or more columns that can uniquely identify rows in a table.
• Example:
  Any key that includes the primary key is a super key. For instance: (employee_id), (employee_id, name) -- Both are super keys if `employee_id` is unique.