Keys in SQL

Keys are essential to uniquely identify rows in a table and establish relationships between tables.

Common Types of Keys:

1. Primary Key

• Uniquely identifies each record in a table.

• Must be unique and NOT NULL.

• Each table can have only one primary key.

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

2. Foreign Key

• A column (or set of columns) that refers to the primary key in another table.

• Used to enforce referential integrity between tables.

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

3. Unique Key

• Ensures all values in a column (or group of columns) are unique.

• Unlike primary key, it can accept one NULL (depending on DBMS).

ALTER TABLE employeesADD CONSTRAINT unique_email UNIQUE (email);

4. Candidate Key

• A set of columns that can uniquely identify a record.

• One candidate key is chosen as the primary key; others can be unique keys.

5. Composite Key (Compound Key)

• A primary key made up of multiple columns combined to uniquely identify rows.

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

6. Super Key

• Any set of columns that uniquely identifies rows. It may contain extra columns beyond what is needed.

If you'd like, I can provide examples or explain how to create and use these keys!