DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workkloads.

Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • Practical Generators in Go 1.23 for Database Pagination
  • Providing Enum Consistency Between Application and Data
  • The Generic Way To Convert Between Java and PostgreSQL Enums
  • JSON-Based Serialized LOB Pattern

Trending

  • Mastering Advanced Traffic Management in Multi-Cloud Kubernetes: Scaling With Multiple Istio Ingress Gateways
  • How to Format Articles for DZone
  • Develop a Reverse Proxy With Caching in Go
  • Scalable System Design: Core Concepts for Building Reliable Software
  1. DZone
  2. Data Engineering
  3. Databases
  4. Database Keys: A Comprehensive Guide

Database Keys: A Comprehensive Guide

Database keys are crucial for organizing and retrieving data efficiently. This guide provides a quick rundown of the main types.

By 
Brij kishore Pandey user avatar
Brij kishore Pandey
·
Sep. 17, 24 · Tutorial
Likes (1)
Comment
Save
Tweet
Share
4.0K Views

Join the DZone community and get the full member experience.

Join For Free

In the world of database management, keys play a crucial role in organizing, accessing, and maintaining data integrity. Whether you're a seasoned database administrator or just starting your journey in data management, understanding the various types of database keys is essential. In this post, we'll explore all the different types of keys, their purposes, and how they contribute to effective database design.

To illustrate these concepts, we'll use a simple university database with the following tables:

  1. Students
  2. Courses
  3. Enrollments
  4. Professors
  5. Departments

Let's dive into each type of key and see how they're applied in our university database.

1. Primary Key

Definition

A column or set of columns that uniquely identifies each row in a table

Characteristics

  • Must be unique for each record
  • Cannot contain NULL values
  • Should be immutable (not change over time)

Example

In our Students table, we use StudentID as the primary key.

SQL
 

CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Email VARCHAR(100) UNIQUE

);


Here, StudentID uniquely identifies each student, ensuring no two students have the same ID.

2. Foreign Key

Definition

A column or set of columns in one table that refers to the primary key in another table

Purpose

Maintains referential integrity and creates relationships between tables

Example

In our Enrollments table, we use StudentID and CourseID as foreign keys.

SQL
 

CREATE TABLE Enrollments (

    EnrollmentID INT PRIMARY KEY,

    StudentID INT,

    CourseID INT,

    EnrollmentDate DATE,

    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),

    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

)


This design ensures that every enrollment record is associated with valid students and courses.

3. Composite Key

Definition

A key that consists of two or more columns to uniquely identify a record

Usage

When no single column can guarantee uniqueness

Example

We could redesign our Enrollments table to use a composite key:

SQL
 
CREATE TABLE Enrollments (

    StudentID INT,

    CourseID INT,

    EnrollmentDate DATE,

    Grade CHAR(2),

    PRIMARY KEY (StudentID, CourseID),

    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),

    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)

);


Here, the combination of StudentID and CourseID forms a composite key, uniquely identifying each enrollment.

4. Candidate Key

Definition

A minimal set of attributes that can uniquely identify a record

Characteristics

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

Example

In our Students table, both StudentID and Email could be candidate keys, as both can uniquely identify a student. We chose StudentID as the primary key, but Email could have been an alternative.

5. Super Key

Definition

Any set of columns that can uniquely identify a record, including unnecessary columns

Difference From Candidate Key

May contain extra attributes not needed for unique identification

Example

In our Students table, super keys could include:

  • {StudentID, FirstName, LastName, Email} 
  • {StudentID, Email} 
  • {Email, FirstName, LastName}

These all include the candidate keys (StudentID or Email) plus additional fields.

6. Alternate Key

Definition

A candidate key that is not selected as the primary key

Purpose

Provides an alternative method of uniquely identifying records

Example

In our Students table, if we choose StudentID as the primary key, then Email becomes an alternate key:

SQL
 
CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Email VARCHAR(100) UNIQUE  -- This is an alternate key

);


The UNIQUE constraint on Email ensures it can also be used to identify students uniquely.

7. Surrogate Key

Definition

An artificial key created solely for the purpose of unique identification

Characteristics

  • Usually auto-generated by the database
  • Has no business meaning

Example

In our Courses table, we might use an auto-incrementing CourseID:

SQL
 
CREATE TABLE Courses (

    CourseID INT PRIMARY KEY AUTO_INCREMENT,

    CourseCode VARCHAR(10) UNIQUE,

    CourseName VARCHAR(100),

    Credits INT

);


CourseID is a surrogate key, while CourseCode might be a more natural identifier.

8. Natural Key

Definition

A key that is formed from data that already exists in the real world

Advantage

Provides meaningful identification

Disadvantage

May change over time

Example

In a Professors table, we might consider using a Social Security Number (though not recommended for privacy reasons):

SQL
 
CREATE TABLE Professors (

    ProfessorID INT PRIMARY KEY,

    SSN CHAR(9) UNIQUE,  -- This could be used as a natural key

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Department VARCHAR(50)

);


9. Compound Key

Definition

A key that consists of two or more columns, where the combination may have some business significance

Difference From Composite Key

Often used when the combined columns have meaning beyond just unique identification.

Example

In a CourseOfferings table:

SQL
 
CREATE TABLE CourseOfferings (

    CourseID INT,

    Semester CHAR(6),  -- e.g., '202301' for Spring 2023

    ProfessorID INT,

    RoomNumber VARCHAR(10),

    PRIMARY KEY (CourseID, Semester),

    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID),

    FOREIGN KEY (ProfessorID) REFERENCES Professors(ProfessorID)

);


The combination of CourseID and Semester forms a compound key, uniquely identifying a course offering while also having business significance.

10. Simple Key

Definition

A key that consists of just one column

Advantage

Easy to implement and use

Example

In our Departments table:

SQL
 
CREATE TABLE Departments (

    DepartmentID INT PRIMARY KEY,

    DepartmentName VARCHAR(100) UNIQUE,

    Building VARCHAR(50),

    Budget DECIMAL(10, 2)

);


DepartmentID is a simple key: a single column uniquely identifying each department.

11. Unique Key

Definition

A key that ensures all values in a column or set of columns are unique

Difference From Primary Key

Can allow NULL values (unless specified otherwise)

Example

In our Students table, Email is an example of a unique key:

SQL
 
CREATE TABLE Students (

    StudentID INT PRIMARY KEY,

    FirstName VARCHAR(50),

    LastName VARCHAR(50),

    Email VARCHAR(100) UNIQUE  -- This is a unique key

);


The UNIQUE constraint ensures that no two students can have the same email address.

Conclusion

Understanding these different types of keys is crucial for effective database design and management. Each type of key serves a specific purpose, from ensuring data integrity to establishing relationships between tables. By carefully selecting and implementing the appropriate keys, you can create efficient, reliable, and scalable database systems.

Remember, the choice of keys can significantly impact your database's performance, maintainability, and ability to represent real-world relationships accurately. Always consider the specific needs of your application and the nature of your data when deciding which keys to use.

This  post is part of our Database Fundamentals series. Stay tuned for more in-depth articles on database management and design.

Database Database design Natural key Data (computing) Data Types

Opinions expressed by DZone contributors are their own.

Related

  • Practical Generators in Go 1.23 for Database Pagination
  • Providing Enum Consistency Between Application and Data
  • The Generic Way To Convert Between Java and PostgreSQL Enums
  • JSON-Based Serialized LOB Pattern

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!