If you are a person related to the IT field or data-related sector, it is very important to understand the way data is stored, related and looked after in databases, especially those that use a relational approach. Handling data structure and efficiency depends on the use of primary and foreign keys. Ensuring relationships between tables are clear, logical and consistent depends on these two types of keys in relational data integrity.
This article clearly describes the difference between primary key and foreign key by using real-life examples and SQL snippets. If you are new to databases or want to refresh your skills, it is important to be able to identify the differences between a primary key and a foreign key.
What is a Primary Key?
First, we need to focus on the primary key. A primary key, in the same way as a passport number or student roll number, labels each row in a table by being one of a kind. A passport number is unique to every individual, isn’t it? It is also important that each row in a table has a different primary key from any other row.
Definition
In a database table, a primary key is a field or group of fields that makes every record stand out uniquely. It ensures that every record has a way to be distinguished from others.
Key Characteristics of a Primary Key
Uniqueness: Every value in a primary key column must be unique.
Not NULL: It can never have NULL values.
Only One Per Table: Each table can have only one primary key.
Ensures Entity Integrity: Prevents duplicate records and maintains data consistency.
Example
Let’s say we have a table of students
Student Table
| StudentID | Name | Age |
|---|---|---|
| 101 | Alice | 20 |
| 102 | Bob | 21 |
| 103 | Charlie | 22 |
At this point, StudentID is the primary key since it is the unique identifier for every student. All students are given IDs that differ from each other.
SQL Example
CREATE TABLE Students ( StudentID INT PRIMARY KEY, Name VARCHAR(50), Age INT );
What is a Foreign Key?
Now, let’s discuss the foreign key. Just as your ID is used as a primary key, a foreign key is a way to refer to someone else’s ID. It helps you reference data from one table in another table.
Definition
A foreign key is a column in a table that connects to the primary key in a different table. It establishes a relationship between one table and the other.
Key Characteristics of a Foreign Key
Can be Duplicated: Unlike primary keys, foreign keys can have duplicate values.
Can be NULL: Depending on constraints, it may or may not allow NULLs.
Multiple Allowed: A table can have multiple foreign keys.
Maintains Referential Integrity: Ensures that relationships between tables remain consistent.
Example
Let’s create another table to store the courses that students take
Courses Table
| CourseID | CourseName |
|---|---|
| 201 | Database Systems |
| 202 | Web Development |
Student_Course Table
| StudentID | CourseID |
|---|---|
| 101 | 201 |
| 102 | 202 |
| 101 | 202 |
Both StudentID and CourseID in the Student_Course table serve as foreign keys to look up Student records in the Students table and Course records in the Courses table. As a result, students relate better to the courses in which they work.
SQL query for the Courses Table
CREATE TABLE Courses ( CourseID INT PRIMARY KEY, CourseName VARCHAR(100) );
SQL query for Student_Course
CREATE TABLE Student_Course ( StudentID INT, CourseID INT, FOREIGN KEY (StudentID) REFERENCES Students(StudentID), FOREIGN KEY (CourseID) REFERENCES Courses(CourseID) );
Key Differences: Primary Key vs. Foreign Key
It is easier to understand the difference between a primary key and a foreign key when we look at it in a table.
| Criteria | Primary Key | Foreign Key |
|---|---|---|
| Uniqueness | Must be unique | Can have duplicate values |
| NULL Values | Cannot be NULL | Can be NULL (unless restricted) |
| Purpose | Uniquely identifies a record | Establishes a relationship with another table |
| Number per Table | Only one per table | Can be multiple |
| Integrity | Ensures entity integrity | Ensures referential integrity |
The above table helps to recognise the difference between primary key and foreign key when designing reliable and logical relational databases.
Importance and Usage
Both primary and foreign keys are important when building and organising relational databases.
Why Primary Keys Matter
A database table is centred on its primary key. Lacking these, figuring out which records are originals can be difficult, and mistakes are easy. They
- Guarantee uniqueness, ensuring every row is distinguishable.
- Speed up data access through indexing.
- Prevent data redundancy and maintain entity integrity.
- Aid in operations like JOINs and queries, giving you clean and fast results.
Imagine you have to get in touch with a student, but you cannot use their roll number — how would you identify the right person among so many others?
Why Foreign Keys Matter
Foreign keys help bring different tables in a database together as one unit. They
- Preserve relationships across tables, like students and the courses they take.
- Prevent invalid data entries, stopping, for instance, a course registration for a non-existent student.
- Enable cascading actions, like auto-deleting related records.
- Help maintain a normalised structure, reducing duplication and confusion.
Visually, foreign keys keep tables linked and help share data, preventing each table from being on its own.
By knowing the difference between primary key and foreign key, data architects can build databases that are both secure and grow with the needs of the users.
Real-World Example
For simplicity, imagine you’re setting up a college management system.
The student table uses StudentID as the primary key.
The courses table uses CourseID as the primary key.
The Student_Course table uses both StudentID and CourseID as foreign keys.
It helps you see who is enrolled in which course without adding extra information.
SQL Example
-
Insert into Students
INSERT INTO Students VALUES (101, 'Alice', 20); INSERT INTO Students VALUES (102, 'Bob', 21); -
Insert into Courses
INSERT INTO Courses VALUES (201, 'Database Systems'); INSERT INTO Courses VALUES (202, 'Web Development'); -
Insert into Student_Course
INSERT INTO Student_Course VALUES (101, 201); INSERT INTO Student_Course VALUES (102, 202); INSERT INTO Student_Course VALUES (101, 202);
In this Example
- The Students table stores the basic details of students. Each student has a unique StudentID.
- Available courses are detailed in the Courses table, each having a unique CourseID.
- The Student_Course table links students to their enrolled courses. Here, StudentID and CourseID act as foreign keys referencing the primary keys in Students and Courses, respectively.
As a result, every student can be linked to several courses at once, and every course can have multiple students, allowing us to view this information in a well-organised and sturdy framework.
Conclusion
Everyone handling databases needs to know the difference between a primary key and a foreign key. To recap:
A primary key gives every record in a table a different identity and prevents duplication or NULL values.
A foreign key builds a link between two tables and helps preserve their relationship.
When you differentiate between primary key and foreign key, your database will be accurate, organised and efficient. Success in database fundamentals depends on mastering these keys.