Pebellslearn by building
0%
RoadmapsMatrixCoursesSandboxSign in
Relational Databases  ›  Lesson

Tables, Rows and Keys

PostgreSQL 14 minA relational database stores data in tables of rows, gives each row a primary key, and links tables with foreign keys instead of copying data.

Tables, Rows and Keys

The bookshop needs to remember its books, its authors and every order, and still have them tomorrow after a restart. Variables in your program vanish when it stops. A database keeps data safe on disk, lets many requests read and write it at once, and refuses data that breaks your rules.

The rule

One thing per row, one key per row, link tables by key.

A relational database, such as PostgreSQL, stores data in tables. Each table holds one kind of thing; each row is one of those things; each column is one fact about it, with a fixed type.

CREATE TABLE authors (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id        INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  price     INTEGER NOT NULL,          -- in rupees
  author_id INTEGER NOT NULL REFERENCES authors(id)
);
  • A primary key identifies exactly one row. No two books share an id, and it is never empty.
  • A foreign key stores another table's primary key to link rows. books.author_id says "this book's author is row N of authors". The database rejects a book whose author_id points at no author.
  • Constraints such as NOT NULL are rules the database enforces for you, whatever code sends the data.

To bring the linked pieces back together, you join on the key:

SELECT books.title, authors.name
FROM books
JOIN authors ON authors.id = books.author_id;

Why not just store the author's name in every book row? Because then one fact lives in many places. Fix a misspelling in one row and miss another, and the database now holds two versions of the truth. Linking by key keeps each fact in one place.

Relational databases also run changes in transactions: a group of statements that either all succeed or all fail. "Create the order and reduce the stock" never half-happens.

The shortcut

For every new piece of data, ask: "Is this a new thing, or a fact about an existing thing?" A new kind of thing gets its own table and primary key. A fact about a thing is a column. A connection between things is a foreign key on the "many" side.

Adding reviews to the bookshopStep 1 of 4

  1. New thing or fact?

    A review is a new kind of thing, with its own text and rating, so it gets its own table: reviews.

Drills

Answer fast — trust the rule.

quick drill

What must be true of a primary key?

quick drill

Each book has one author and each author writes many books. Where does the foreign key go?

quick drill

You insert a book with author_id = 99, but there is no author with id 99. What happens?

quick drill

Why link books to authors by id instead of copying the author's name into each book?

Remember

  • One thing per row, one key per row, link tables by key.
  • New kind of thing → table; fact → column; connection → foreign key on the "many" side.
  • Let the database enforce rules with constraints; your code is not the only thing that writes data.

Practice — level up

0/1 solved

Write your answer, then compare it with a rubric and a model answer.

ExplainBooks and authors

Design tables for the bookshop's books and authors, where each book has one author and an author can write many books. Name the primary keys, the foreign key, and explain why you do not simply store the author's name in every book row.