This lecture introduces the concept of Primary Keys.
Welcome to Part 3 of our series. So far, we have worked with single tables. But the real power of relational databases comes from connecting, or relating, multiple tables together. The first and most important concept for linking tables is the Primary Key.
What is a Primary Key?
A primary key is a column (or a set of columns) in a table whose value uniquely identifies each row. Think of it like a Social Security Number for a person or a serial number for a product. No two people have the same SSN, and no two products have the same serial number.
A primary key must follow two simple rules:
- It must be UNIQUE. No two rows in the table can have the same primary key value.
- It must NOT be NULL. Every row must have a value for the primary key.
Why Do We Need Them?
Imagine our `Contacts` table. What if we have two people named ‘John Smith’? If we wanted to update the address for one of them, how would the database know which John Smith to update? Using the name is unreliable.
This is why we’ve been using a column like `ContactID` or `StudentID` in our tables. This ID column serves as the perfect primary key. Even if two contacts have the same name, they will have a different `ContactID`. This gives us a guaranteed way to uniquely identify every single row.
Defining a Primary Key
We have already been doing this! When we create a table, we can specify a column as the primary key. The best way to do this in SQLite is as follows:
CREATE TABLE Students (
StudentID INTEGER PRIMARY KEY,
FirstName TEXT,
LastName TEXT
);
By defining `StudentID` as INTEGER PRIMARY KEY, we are telling SQLite three things:
- This column is the primary key for the `Students` table.
- It must contain a unique integer value for every row.
- And most importantly: SQLite will automatically generate a new, unique, sequential number for this column whenever we insert a new row without specifying an ID. This is called auto-incrementing and it is incredibly convenient.
The Foundation for Relationships
The primary key is the anchor of a table. It provides a stable, unique identifier for each record that will never change. In the next lectures, we will see how we can reference these primary keys from other tables to build powerful relationships.
In the Next Lecture…
We will learn about the other side of the coin: the Foreign Key. A foreign key is a column in one table that points to the primary key of another table, creating a link between them.
