What are the types of keys in database tables?

There are several main types of keys:

• PRIMARY KEY: Uniquely identifies each record in a table. There can be only one primary key in a table.
• FOREIGN KEY: Establishes a link between two tables by referencing the primary key in another table. It maintains referential integrity.
• UNIQUE KEY: Ensures the uniqueness of values in a column or a group of columns, but unlike the primary key, it can contain NULLs (if allowed by the column definition).
• CANDIDATE KEY: Any column or set of columns that can uniquely identify a row in a table. The primary key is one of the candidate keys.
• SUPER KEY: A set of one or more columns that uniquely identify a row in a table. It includes candidate keys but can also contain additional columns that are not necessary for uniqueness.
• COMPOSITE KEY: A key consisting of two or more columns used together to uniquely identify a row. It can be a primary, foreign, or unique key.

-- Example of a table with different types of keys
CREATE TABLE Users (
    user_id INT PRIMARY KEY, -- Primary key
    username VARCHAR(50) UNIQUE, -- Unique key
    email VARCHAR(100),
    city_id INT,
    -- Foreign key referencing the Cities table
    FOREIGN KEY (city_id) REFERENCES Cities (city_id)
);

CREATE TABLE Cities (
    city_id INT PRIMARY KEY,
    city_name VARCHAR(50)
);