25Sep, 2023
Mastering Database Design: Primary Key vs. Foreign Key Distinctions

One of the most important elements of a relational database is the key, it helps in maintaining the relationship between the tables and in uniquely identifying the data from each table. The foreign key is used to identify the relationship between the tables by using the primary key of one table, which works as a foreign key to another table, whereas the primary key is a key that helps in uniquely identifying the tuple of the database. Let’s now get into more detail about both of them.

what is primary key

Imagine you have a huge library filled with books, and each book has its own unique identification number. This number is like a special code that helps you quickly find a specific book on the shelf. In the world of databases, a “Primary Key” is like that special identification number for each row of data in a table.

Let’s break it down in simple terms:

Database Tables:

Think of a database as a digital library where you store information. Just like the library has shelves for different types of books, a database has tables for organizing different types of information, like a table for storing information about customers, another for products, and so on.

Rows and Columns:

In each table, you have rows (like rows of books on a shelf) and columns (like the different pieces of information you want to store, such as a customer’s name, email, and address).

Primary Key:

The Primary Key is like the library’s book identification number. It’s a special column in a table that ensures each row has a unique identifier. This means no two rows can have the same Primary Key value, just like no two books in a library can have the same identification number.

Why It Matters:

Having a Primary Key is essential because it helps the database quickly locate and manage specific pieces of information. When you want to find or update a specific row in the table (like a particular customer’s data), the Primary Key makes the process fast and efficient. It’s like finding a book on a shelf using its unique identification number.

In summary, a Primary Key in SQL is like a unique identification number for each piece of information in a database table. It ensures that no two rows have the same identifier, making it easy to find and manage data effectively.

What is Foregin Key ?

Picture a School Database:

Imagine you’re the principal of a school, and you have a computer system to keep track of all the students and their courses. Your database is like a digital school office where you store information about students, classes, and teachers.

The Student-Teacher Connection:

In this school, each student is assigned to a teacher, just like how you assign students to different classrooms. Now, think of your database as a big filing cabinet where you organize all this information.

Foreign Key as a Student ID Card:

A “Foreign Key” in this scenario is like a student’s ID card. It’s a special card given to each student that contains their unique student number. This student number is essential because it connects the student to their teacher.

Why It’s Important:

Here’s why this student ID card (Foreign Key) is crucial:

Let’s say you want to find out which teacher is responsible for a specific student, like Johnny. Instead of searching through all the classrooms and asking everyone, you can simply look at Johnny’s ID card (Foreign Key). It tells you that Johnny’s teacher is Ms. Smith.

Connecting the Dots:

In the world of databases, the Foreign Key works the same way. It connects related information in different tables. So, when you want to know which teacher belongs to a particular student, the Foreign Key makes it easy. It’s like having a magical ID card that connects students to their teachers in your school database.

To sum it up, a Foreign Key is like a student’s ID card in a school database. It helps connect students to their teachers, making it easy to find who’s responsible for each student’s education. It’s the key to connecting the dots in your database world!

Create primary key and foreign key in SQL

Creating a Primary Key:

A primary key uniquely identifies each row in a table. Here’s how to create a primary key when creating a new table:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

Creating a Foreign Key and foreign Key:

A foreign key establishes a link between two tables, typically between a primary key in one table and a related column in another table. Here’s how to create a foreign key:

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName VARCHAR(50)
);

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Insert Values in Table

-- Insert 10 Indian student records into the existing Students table
INSERT INTO Students (StudentID, FirstName, LastName)
VALUES
    (11, 'Amit', 'Kumar'),
    (12, 'Rajesh', 'Singh'),
    (13, 'Priya', 'Sharma'),
    (14, 'Sneha', 'Patel'),
    (15, 'Arun', 'Gupta'),
    (16, 'Neha', 'Verma'),
    (17, 'Sanjay', 'Yadav'),
    (18, 'Anita', 'Jha'),
    (19, 'Rakesh', 'Malik'),
    (20, 'Pooja', 'Rao');
insert into course data 
-- Insert sample data into the Courses table
INSERT INTO Courses (CourseID, CourseName)
VALUES
    (1, 'Mathematics'),
    (2, 'History'),
    (3, 'Computer Science'),
    (4, 'English'),
    (5, 'Physics'),
    (6, 'Biology'),
    (7, 'Chemistry'),
    (8, 'Art'),
    (9, 'Music'),
    (10, 'Geography');
Insert Into Enrollment Table 
INSERT INTO Enrollments (EnrollmentID, StudentID, CourseID)
VALUES
    (1, 1, 3),
    (2, 2, 1),
    (3, 3, 5),
    (4, 4, 2),
    (5, 5, 7),
    (6, 6, 4),
    (7, 7, 6),
    (8, 8, 8),
    (9, 9, 9),
    (10, 10, 10);

Aspect

Primary Key

Foreign Key

Purpose

Uniquely identifies records in a table.

Establishes a link between two tables.

Uniqueness

Must be unique within the table.

Can have duplicate values in the table.

Nullable

Typically, not nullable (depends on DBMS).

Can be nullable (allows NULL values).

Number per Table

Only one primary key per table.

Multiple foreign keys can exist in a table.

Indexing

Automatically indexed by the DBMS.

Requires indexing for performance.

Data Type           

Usually numeric or a unique identifier.   

Matches the data type of the referenced primary key.

Constraint

Enforces data integrity and uniqueness.

Ensures referential integrity.

Ownership

Belongs to the table in which it’s defined

Belongs to the table that references it.

Relationship

Doesn’t establish a relationship but identifies records.

Establishes a relationship with a primary key in another table.

Usage in Queries

Used for joins and as a reference point for other tables.

Used to navigate between related tables.

 

FAQ

A primary key is a field or set of fields in a database table that uniquely identifies each record within that table.

A primary key ensures data integrity by preventing duplicate or null values in the key field(s). It also provides a way to identify and relate records across tables.

No, a table can have only one primary key. However, a composite primary key consisting of multiple fields is possible.

Examples include Social Security numbers, email addresses, and unique numerical identifiers like product IDs or customer IDs.

No, while integers are commonly used, a primary key can be of various data types, including strings or even composite keys made up of multiple fields.

It depends on the database management system (DBMS). Some DBMSs allow nulls in a primary key, but it’s generally not recommended.

 

Yes, most DBMSs automatically create an index on the primary key field(s) for faster data retrieval.

A foreign key is a field in one table that is used to establish a link to the primary key in another table, creating a relationship between them.

Foreign keys enforce referential integrity, ensuring that data in related tables remains consistent. They also facilitate data retrieval from multiple related tables.

Yes, a table can have multiple foreign keys, each linking to a different primary key in other tables.

This action violates referential integrity and is typically not allowed by the DBMS, resulting in an error.

 

Yes, a foreign key can reference a unique constraint, but it’s most commonly used to reference a primary key.

It’s a good practice to index foreign key columns for performance reasons, but it’s not mandatory.

Unlocking Business Insights: SQL Analysis of Sales and Revenue