What are keys in a database?

A key in a database is one or more fields (columns) of a table that serve to uniquely identify records (rows) or establish relationships between tables.

Types of keys:

• Primary Key (PK):
  • Uniquely identifies each record in the table.
  • Cannot contain NULL values.
  • There can be only one primary key in a table.
• Foreign Key (FK):
  • Establishes a relationship between two tables by referencing the primary key or a unique key of another table.
  • Ensures referential integrity by guaranteeing the existence of the referenced record.
• Unique Key (UK):
  • Ensures the uniqueness of values in the specified fields but can contain NULL values (usually no more than one NULL).
  • There can be multiple unique keys in a table.
• Candidate Key:
  • Any minimal set of fields capable of uniquely identifying a record.
  • One candidate key is selected as the primary key.
• Super Key:
  • A set of fields that can uniquely identify a record. A candidate key is a minimal super key.

Example of using a foreign key:

CREATE TABLE Orders (
    order_id INT PRIMARY KEY, -- Primary key for Orders table
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) -- Foreign key referencing Customers table
);

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY, -- Primary key for Customers table
    customer_name VARCHAR(255)
);