Thursday, August 14, 2025
EN | عربي
قواعد البيانات

what's SQL Key?

Tuesday, June 10, 2025 32 مشاهدة

know what's SQL key and his types

In SQL, a key is a column or a combination of columns that uniquely identifies a record in a table. Keys are used to enforce integrity constraints and to establish relationships between tables. There are several types of keys in SQL


Example:

Primary Key

A Primary Key is a column (or a set of columns) that uniquely identifies each row in a table. It cannot contain duplicate or NULL values.

CREATE TABLE Employee (
employee_id INT PRIMARY KEY, -- Uniquely identifies each employee
First_Name VARCHAR (50), 
Last_Name VARCHAR (50)
);


Foreigen key

A Foreign Key is a column (or a group of columns) in one table that references the primary key in another table. This establishes a relationship between tables and enforces referential integrity.

CREATE TABLE Departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(100)
);

CREATE TABLE Employees (
    employee_id   INT PRIMARY KEY,
    first_name    VARCHAR(50),
    last_name     VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES Departments(department_id)
);


Unique Key

A Unique Key ensures that all the values in a column (or a combination of columns) are distinct. Unlike the primary key, a unique key column may allow a single NULL (depending on the DBMS).

CREATE TABLE users (
user_id INT PRIMARY KEY
UserName VARCHAR (50) UNIQUE,
EMAIL VARCHAR (50) UNIQUE
);


Candidate Key

A Candidate Key is any column (or combination of columns) that can uniquely identify a row. A table can have multiple candidate keys. One of these is chosen as the primary key, and the others are known as alternate keys.

CREATE TABLE Users (
    user_id  INT,            -- Candidate Key 1
    username VARCHAR(50),    -- Candidate Key 2
    email    VARCHAR(100),   -- Candidate Key 3
    PRIMARY KEY (user_id),   -- Chosen as the Primary Key
    UNIQUE (username),       -- Alternate Key (remaining candidate)
    UNIQUE (email)           -- Alternate Key (remaining candidate)
);

Alternate Key

An Alternate Key is simply a candidate key that was not selected as the primary key. In the previous example, both username and email are alternate keys.

Example Explanation:

  • In the Users table above, since user_id is used as the primary key, username and email serve as alternate keys.


Composite Key

A Composite Key is a primary key that consists of more than one column. The combination of these columns is used to uniquely identify a row.

CREATE TABLE OrderDetails (
    order_id   INT,
    product_id INT,
    quantity   INT,
    PRIMARY KEY (order_id, product_id)  -- Composite key made of two columns
);


Super Key

A Super Key is any combination of columns that uniquely identifies a row in a table. It might include extra columns that are not necessary for unique identification (i.e., it may not be minimal).

CREATE TABLE Students (
    student_id INT,
    name       VARCHAR(50),
    age        INT,
    PRIMARY KEY (student_id)
);

notes outlining each type of key in SQL databases:

Primary Key

  • Uniqueness: Must be unique for every record and cannot accept NULL values.
  • Identity: Acts as the main column representing the identity of the table.
  • Note: Typically, only one primary key is chosen per table.

Foreign Key

  • Relationship: Used to link different tables by referencing the primary key of another table.
  • Referential Integrity: Ensures the integrity between tables is maintained.
  • Note: When deleting or updating records in the related table, handling policies (such as CASCADE or RESTRICT) must be considered.

Unique Key

  • Distinct Values: Ensures that the values in a column (or a combination of columns) are not duplicated. It is similar to a primary key but may allow a NULL value, depending on the DBMS.
  • Note: Allows you to specify a column or set of columns for ensuring data uniqueness without setting it as the primary key.

Candidate Key

  • Definition: Any set of columns that has the ability to uniquely identify a record in the table.
  • Multiple Options: A table can have more than one candidate key. One candidate is chosen as the primary key, while the others are referred to as alternate keys.
  • Note: A candidate key is a minimal version of a super key, meaning it does not include any extra columns that are not necessary.

Alternate Key

  • Definition: Candidate keys that were not chosen to be the primary key.
  • Note: They retain the same properties of candidate keys and are important for providing additional options for ensuring uniqueness.


Composite Key

  • Combination: Consists of more than one column together to uniquely identify a record.
  • Note: Used when a single column is not sufficient to uniquely identify a record.

Super Key

  • Broad Uniqueness: Any combination of columns that guarantees the uniqueness of each record, even if it includes extra columns that are not necessary for unique identification.
  • Note: Every candidate key is a super key, but not every super key qualifies as a candidate key because it might include additional, non-essential columns.

In summary, these notes are the essential points you need to know when documenting or outlining the types of keys in SQL. They help clarify the distinct differences and the specific uses of each key type in designing a robust database.


← Back to Articles More from Databases
Keyboard