SQL Keys

Level : Beginner
Mentor: Shailendra Chauhan
Duration : 00:03:00

Super Key

A super key is a set of one or more attributes (columns) that can uniquely identify a tuple (row) in a relation (table). It may contain extra attributes that are not strictly required for uniqueness.

Example

-- Creating a super key
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Email VARCHAR(100),
    PhoneNumber VARCHAR(15),
    PRIMARY KEY (EmployeeID, Email)
);

Candidate Key

A candidate key is a minimal super key, meaning it is a set of attributes that uniquely identifies each tuple without any unnecessary attributes.

Example

-- Creating a candidate key
CREATE TABLE Students (
    StudentID INT,
    SSN VARCHAR(9) UNIQUE,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    PRIMARY KEY (StudentID)
);

Primary Key

A primary key is a specific candidate key chosen as the main method of uniquely identifying tuples within a table. It enforces uniqueness and ensures that no NULL values are allowed in the key columns.

Example

-- Creating a primary key
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

Alternate Key

An alternate key is a candidate key that was not chosen as the primary key. While it can still uniquely identify tuples, it is not used as the primary means of identification.

Example

-- Creating an alternate key
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    UPC VARCHAR(12) UNIQUE,
    ProductName VARCHAR(100)
);

Composite/Compound Key

A composite or compound key is a key composed of two or more attributes. Together, these attributes uniquely identify tuples, but individually, they may not.

Example

-- Creating a composite key
CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    PRIMARY KEY (OrderID, ProductID)
);

Unique Key

A unique key enforces uniqueness on a set of attributes but does not necessarily serve as the primary means of identification like a primary key.

Example

-- Creating a unique key
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    SocialSecurityNumber VARCHAR(9) UNIQUE,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Foreign Key

A foreign key is a field or set of fields in one table that references the primary key of another table, establishing a relationship between the two tables.

Example

-- Creating a foreign key
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Surrogate Key

A surrogate key is an artificially generated key, typically an integer, used as the primary key in a table. It has no inherent business meaning and is often used when natural keys are not suitable or practical.

Example

-- Creating a surrogate key
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Self-Referencing Foreign Key

A self-referencing foreign key is a foreign key within a table that references its own primary key, creating a hierarchical or recursive relationship within the same table.

Example

-- Creating a self-referencing foreign key
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    ManagerID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    FOREIGN KEY (ManagerID) REFERENCES Employees(EmployeeID)
);

Unique Constraint

A unique constraint ensures that the values in a specific column or combination of columns are unique across all rows in a table, similar to a unique key.

Example

-- Creating a unique constraint
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductCode VARCHAR(20) UNIQUE,
    ProductName VARCHAR(100),
    Price DECIMAL(10, 2)
);
Self-paced Membership
  • 22+ Video Courses
  • 800+ Hands-On Labs
  • 400+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A Courses
  • 10+ Real-world Projects
  • Career Coaching Sessions
  • Email Support
Upto 60% OFF
Know More
Still have some questions? Let's discuss.
CONTACT US
Accept cookies & close this