Keys in SQL

Keys in SQL: A Comprehensive Guide

Introduction

Keys in SQL are crucial attributes or sets of attributes that help uniquely identify a record (or a row) in a table. They play an essential role in database design, ensuring data integrity, establishing relationships between tables, and improving query performance. This article explores various types of keys in SQL, their importance, and how they are used.

Types of Keys

Primary Key

Definition: A primary key is a column or a set of columns that uniquely identifies each row in a table.

Characteristics:

• Must contain unique values.
• Cannot contain NULL values.
• There can be only one primary key per table.

Example:

<script type="text/sql">
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);
</script>

  

Composite Key

Definition: A composite key is a primary key that consists of more than one column.

Example:

<script type="text/sql">
CREATE TABLE Orders (
    OrderID INT,
    ProductID INT,
    Quantity INT,
    PRIMARY KEY (OrderID, ProductID)
);
</script>

  

Foreign Key

Definition: A foreign key is a column or a set of columns in one table that references the primary key in another table. It establishes a relationship between the two tables.

Characteristics:

• Ensures referential integrity.
• Helps maintain consistency across related tables.

Example:

<script type="text/sql">
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
</script>

  

Candidate Key

Definition: A candidate key is a column or a set of columns that can uniquely identify a row in a table. Each candidate key can serve as a primary key.

Characteristics:

• Must contain unique values.
• Cannot contain NULL values.
• A table can have multiple candidate keys.

Example:

<script type="text/sql">
CREATE TABLE Employees (
    EmployeeID INT,
    SSN VARCHAR(11),
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    PRIMARY KEY (EmployeeID),
    UNIQUE (SSN)
);
</script>

  

Alternate Key

Definition: An alternate key is a candidate key that is not chosen as the primary key.

Example:

<script type="text/sql">
CREATE TABLE Employees (
    EmployeeID INT,
    SSN VARCHAR(11),
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    PRIMARY KEY (EmployeeID),
    UNIQUE (SSN)
);
</script>

  

In this example, SSN is an alternate key.

Unique Key

Definition: A unique key is a column or a set of columns that must contain unique values, but unlike the primary key, it can contain NULL values.

Example:

<script type="text/sql">
CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Email VARCHAR(100) UNIQUE
);
</script>

  

Super Key

Definition: A super key is a set of one or more columns that can uniquely identify a record in a table. It can include redundant attributes.

Example:

<script type="text/sql">
CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(100),
    CategoryID INT,
    PRIMARY KEY (ProductID),
    UNIQUE (ProductID, ProductName)
);
</script>

  

In this example, (ProductID, ProductName) is a super key.

Importance of Keys

• Data Integrity: Keys ensure that each record in a table is unique and valid.
• Relationships: Foreign keys establish and enforce relationships between tables.
• Data Retrieval: Keys improve the efficiency of data retrieval and indexing.
• Referential Integrity: Foreign keys help maintain the integrity of data across related tables.

Practical Examples

Creating a Primary Key

<script type="text/sql">
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName VARCHAR(50)
);
</script>

  

Creating a Foreign Key

<script type="text/sql">
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    DepartmentID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);
</script>

  

Creating a Composite Key

<script type="text/sql">
CREATE TABLE Enrollments (
    StudentID INT,
    CourseID INT,
    EnrollmentDate DATE,
    PRIMARY KEY (StudentID, CourseID)
);
</script>

  

Conclusion

Keys in SQL are fundamental elements that ensure data integrity, establish relationships, and enhance query performance. By understanding and effectively using different types of keys, you can design robust and efficient databases that maintain consistency and integrity of data.