DataslopePrimary Keys
Learn why every table needs a stable, unique identity, and how primary keys make rows safe to reference.
Suppose your app has two customers named Taylor Kim. Same city, same signup date, maybe even the same birthday. If an order belongs to "Taylor Kim," which row should it point to?
That is the identity problem. A table does not just need values; it needs a reliable way to say this exact row, not that similar-looking one.
Rows need identity
Human descriptions are often messy. Names repeat. Titles change. Email addresses get corrected. A row needs an identity that survives all of that.
A primary key is the table's answer to this problem. It is the column, or group of columns, whose job is to identify each row.
Now there are not two vague Taylors. There is customer 101 and
customer 102. The values can look similar everywhere else, but the
identity is still clear.
The two guarantees
A primary key matters because PostgreSQL enforces two guarantees:
- Unique — no two rows can have the same key value.
- Not null — every row must have a key value.
Those two rules are a design promise: every row can be pointed at, and no pointer has to mean "maybe this row, maybe that one."
A primary key is not just convenient
A primary key is part of the meaning of the table. It says what counts as one distinct thing in your model: one customer, one order, one invoice, one enrollment, one saved address.
Quick check
Why is a primary key a design decision, not just a SQL detail?
Because it decides which rows are shown first in query results.
Because it defines how the table tells one real-world thing apart from another.
Because it makes every column in the table unique.
Because it prevents anyone from updating non-key columns.
Watch PostgreSQL protect identity
Run this block. The first row creates customer 1. The second insert
tries to reuse the same primary key, so PostgreSQL rejects it.
The error is useful. It is the database refusing a design state where two rows claim to be the same thing.
Composite primary keys
A primary key does not have to be one column. A composite primary key uses multiple columns together.
This is common when the row represents a relationship. In a class
roster, neither student_id nor course_id is unique by itself. A
student can take many courses, and a course can have many students. But
the pair should appear only once.
The key is the combination: student 7 in course 42. That design says
"there can be only one enrollment row for this student in this course."
Stability matters
A primary key should rarely, ideally never, change. If many other tables point to it, changing the key means changing every reference too.
That is why many designs use an artificial id: not because numbers are
magical, but because a generated id can stay stable even when human data
changes.
Quick check
In an enrollments table, why might (student_id, course_id) be a good composite primary key?
Because each student can only ever take one course.
Because each course can only ever have one student.
Because the pair identifies one specific student-course enrollment.
Because composite keys allow null values in the key columns.
Check your understanding
What problem does a primary key solve first?
It stores long text more efficiently.
It automatically creates a report for each row.
It gives every row a stable way to be distinguished from every other row.
It makes duplicate names impossible in the table.
Which two guarantees does PostgreSQL enforce for a primary key?
The value must be short and lowercase.
The value must be unique, and it must not be null.
The value must be copied into every other table.
The value must be visible to users.
Why is name usually a weak primary key for a customers table?
PostgreSQL cannot compare text values.
Names are always private and cannot be stored.
Names can repeat, change, or be corrected over time.
Names make joins impossible in SQL.
What does a composite primary key use to identify a row?
The first non-null column in the table.
Two or more columns considered together as the row's identity.
A hidden timestamp created by PostgreSQL.
Every text column in alphabetical order.