Primary Keys and Foreign Keys

In relational databases, Primary Keys and Foreign Keys play a crucial role in maintaining the integrity and reliability of the data.

Primary Key

A primary key is a column (or a set of columns in some cases), in a table that uniquely identifies every row in that table. Primary keys have a few key characteristics:

• Uniqueness: Each row in the table will have a unique primary key value. No two rows can have the same primary key value.
• Non-Null: Primary keys cannot contain NULL values. A primary key column must always contain a value.
• Stability: Once a row is given a primary key, the value of the primary key should not be changed over time.
• Limit: Each table can have only one primary key.

For example, consider a table Students:

Students

StudentID | StudentName | StudentAge
--------- | ----------- | ----------
1         | John        | 20
2         | Maria       | 22
3         | Steve       | 19

In this table, StudentID is a primary key because it uniquely identifies every row in the table.

Foreign Key

A foreign key is a column (or a set of columns), in a table that is used to establish a link between the data in two tables. The foreign key in one table points to a primary key in another table.

The main role of foreign keys is to control the data that can be stored in the foreign key table. In other words, foreign keys maintain referential integrity in the database. They ensure that there are no actions taken on the data that would destroy links between tables and cause bad data.

For example, consider a second table Enrollments:

Enrollments

EnrollmentID | CourseName | StudentID
------------ | ---------- | ----------
1            | Math       | 1
2            | English    | 1
3            | Math       | 2
4            | Science    | 3

In this table, StudentID is a foreign key that points to the StudentID primary key in the Students table.

Example Test Question

Prepare a list that shows each student and the courses they’ve enrolled in.

Answer

To solve this, we need to JOIN the Students and Enrollments tables based on the StudentID which is a common field in both tables (a primary key in the Students table and a foreign key in the Enrollments table).

Here is the SQL query to achieve this:

SELECT Students.StudentName, Enrollments.CourseName
FROM Students
INNER JOIN Enrollments
ON Students.StudentID = Enrollments.StudentID;

This would return the following result:

StudentName | CourseName
----------- | ----------
John        | Math
John        | English
Maria       | Math
Steve       | Science

This result shows each student and the course they’ve enrolled in, which is what the question asked for. This was achieved by joining the Students and Enrollments tables using the relationship established by the primary key (StudentID in the Students table) and the foreign key (StudentID in the Enrollments table).