Skip to content
Tutorials Center
My learning

Topics

Lesson 2 of 125 ยท Getting Started

Databases, Tables and Keys

Learn how relational databases organize data into tables, rows and columns, how primary and foreign keys link tables, and how to read a schema in SQLite.

In the first lesson you queried a bookstore database without worrying about how it was built. Now it's time to look under the hood. Every SQL query you will ever write depends on a few simple ideas: data lives in tables, each row has a key that identifies it, and tables point at each other through those keys.

Once these ideas click, a database stops being a mysterious box and becomes something you can read like a map. By the end of this lesson you will be able to open an unfamiliar database, list its tables, inspect their columns and see how they connect, all with SQL.

What a relational database is

A database is an organized collection of data that a program can store, search and change safely. The program that manages it is called a database management system (DBMS). SQLite, PostgreSQL, MySQL and SQL Server are all database management systems.

Most of them are relational: they store data in tables and let you combine those tables using shared values. The idea goes back to a 1970 paper by the computer scientist Edgar F. Codd, who described data as mathematical relations. That word is where "relational" comes from, and it's why some books call a table a relation.

The relational model has lasted for more than fifty years because it separates what you want from how the computer finds it. You describe the result in SQL, and the database works out the fastest way to produce it.

Tables, rows and columns

A table holds data about one kind of thing: customers, products, orders. It is made of:

  • Columns, which are defined when the table is created. Each column has a name and a type, such as INTEGER or TEXT, and holds one fact about every row.
  • Rows, which are the actual records. A shop might have 26 products today and 2,000 next year, but the columns stay the same.
  • Cells, the single value where a row meets a column.

You will meet several names for the same ideas:

Everyday term Formal (relational) term Also called
Table Relation Entity set
Row Tuple Record
Column Attribute Field

The examples in this lesson use the shop sample database: an online store with categories, products, customers, orders and order items. Start with its smallest table:

SQL The categories table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Two columns, six rows. Unlike a spreadsheet, a table has no built-in row order. The rows above happen to come back in id order, but nothing guarantees that. When order matters, you ask for it with ORDER BY, which gets its own lesson.

A good table also follows a simple rule: one row per thing, one value per cell. A product row holds one price, not "34.90 or 29.90 on sale". When a cell needs several values, that's a sign the data belongs in a separate table. You'll see this idea formalized later as normalization.

Primary keys: a unique name for every row

Two customers can share a first name, a last name and even a city. The database still needs a way to tell them apart, so every well-designed table has a primary key: a column (or group of columns) whose value is different in every row and is never missing.

In the shop database, every table has an id column declared as INTEGER PRIMARY KEY. Product 6 is always the Ultralight Tent, no matter how its name or price changes later.

A primary key comes with two guarantees:

  • Unique: no two rows can have the same key.
  • Not missing: every row must have one.

The database enforces the first guarantee for you. This example creates a small table, inserts two rows, and then tries to reuse an id. It fails on purpose:

SQL Duplicate keys are rejected

Press Run (or Ctrl + Enter) to see the result here.

The first two inserts succeed, and the third stops the run with Error: UNIQUE constraint failed: planets.id. Change the last id to 3 and run it again to see it pass.

Most tables use a meaningless number as their key, called a surrogate key. It never needs to change, even when a customer changes their email or a product is renamed. Sometimes a real-world value works as a key too, such as a country code or a course code like MATH101; that's a natural key. The lesson on keys compares the two in depth.

INTEGER PRIMARY KEY numbers itself in SQLite. If you leave the id out of an INSERT, SQLite picks the next free number. Other databases use SERIAL, IDENTITY or AUTO_INCREMENT for the same job, as you'll see in Auto-Increment and Identity Columns.

Foreign keys: how tables point at each other

Look at the products table. Instead of repeating the category name in every row, each product stores a category_id:

SQL Products point to categories ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

This run has two queries, so you get two result tables. The four products all have category_id 4, and the second query shows that category 4 is Audio. A column like category_id, which holds the primary key of a row in another table, is a foreign key.

Foreign keys bring big advantages:

  • No repeated data. The word "Audio" is stored once. Renaming the category means changing one row, not every product.
  • Fewer mistakes. A database can refuse a product whose category_id doesn't exist.
  • Flexible questions. Because tables share keys, you can combine them in any direction with a join.

Here's a preview of a join, which matches every product with its category in a single result. You don't need to understand the syntax yet:

SQL A first look at a join ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Enforcing foreign keys in SQLite

A foreign key declared with REFERENCES is a promise that the value exists in the other table. Most databases always check that promise. SQLite, for backward compatibility, only checks it after you turn the check on for your connection with PRAGMA foreign_keys = ON;. This example does that and then inserts a product into a category that doesn't exist, so it ends with an error on purpose:

SQL A broken reference ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The run stops with Error: FOREIGN KEY constraint failed. Delete the PRAGMA line and run it again: the insert now succeeds with OK, 1 row affected. even though category 99 doesn't exist. That's why real SQLite applications switch the setting on every time they connect. The full story is in Foreign Keys.

How tables relate

Keys let you model the three kinds of relationship that appear in almost every system:

Relationship Example in the shop How it's stored
One-to-many One category has many products products.category_id points to categories.id
One-to-many One customer places many orders orders.customer_id points to customers.id
Many-to-many An order contains many products, and a product appears in many orders A third table, order_items, with order_id and product_id

The order_items table is a junction table. Each row says "this order contains this product, in this quantity, at this price". It has its own id, plus two foreign keys. You'll design tables like this yourself in One-to-Many and Many-to-Many.

Schemas: the blueprint of a database

The schema is the structure of a database: its tables, their columns and types, the keys, and other rules. The data changes every second; the schema changes rarely, usually through planned migrations.

The word has a second meaning in some systems, so watch for it:

  • In PostgreSQL and SQL Server, a schema is also a namespace inside a database, like a folder. Tables are written as schema.table, for example public.products or dbo.products.
  • In MySQL, "schema" is simply another word for "database".
  • In SQLite, a database is one file, and its main schema is called main.

Reading a schema in SQLite

You'll often work with a database someone else designed. SQLite describes itself in a built-in table named sqlite_schema, with one row for every table, index, view and trigger:

SQL List the tables ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

You'll see five tables and also a few entries named like sqlite_autoindex_customers_1. Those are indexes SQLite created automatically to enforce UNIQUE columns, such as each customer's email. Older code and tutorials call this table sqlite_master; both names work.

The sql column stores the exact CREATE TABLE statement that built each table, which is often the quickest way to learn a table's rules:

SQL The statement behind a table ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

For a tidy, one-row-per-column view, use PRAGMA table_info. A pragma is an SQLite-specific command for reading or changing settings and metadata:

SQL Inspect columns and foreign keys ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The first result lists every column of products with its type, whether it's NOT NULL (notnull is 1), its default value and whether it belongs to the primary key (pk is 1). The stock column shows a default of 0. The second result shows that order_items has two foreign keys: product_id points to products.id and order_id points to orders.id.

Pragmas can also be used like tables, which means you can filter them with WHERE:

SQL Required columns only ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

notnull is in double quotes here because NOTNULL is also an SQL keyword. You'll learn the quoting rules in SQL Syntax and Style.

Reading a schema in other databases

Every major database can describe itself, but the commands differ. The standard way is the information_schema views, supported by PostgreSQL, MySQL and SQL Server (not SQLite):

SQL
-- PostgreSQL, MySQL, SQL Server
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'products'
ORDER BY ordinal_position;

Their command-line clients also have shortcuts:

Task SQLite (sqlite3) PostgreSQL (psql) MySQL (mysql)
List tables .tables \dt SHOW TABLES;
Describe a table .schema products \d products DESCRIBE products;

The sample databases on this site

Every lesson picks one of four sample databases. Each run starts from a fresh copy, so feel free to insert, update and delete as much as you like.

Database Tables Good for
bookstore authors, books, customers, orders First queries; small and easy to see whole
shop categories, products, customers, orders, order_items Filtering, grouping, joins and sales reports
company departments, employees, projects, assignments Self joins (each employee has a manager_id) and hierarchies
school students, courses, teachers, enrollments, terms Grades, rankings and many-to-many data

Run the sqlite_schema query above against any of them whenever you want a reminder of what's inside.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which statement about a primary key is true?

    A primary key identifies each row, so it must be unique and never missing. It can be text or span several columns, and a table has at most one.
  2. 2. In the shop database, what is orders.customer_id?

    It holds the id of the customer who placed the order, which makes it a foreign key to customers.id.
  3. 3. How does the shop database store which products are in which orders?

    Orders and products have a many-to-many relationship, so a third table records each pairing in its own row.
  4. 4. In SQLite, which query lists all tables, indexes, views and triggers?

    sqlite_schema (also known as sqlite_master) is SQLite's catalog. SHOW TABLES is MySQL and information_schema isn't available in SQLite.
  5. 5. You insert a product with category_id 99 into a fresh SQLite connection without any PRAGMA. Category 99 doesn't exist. What happens?

    SQLite checks foreign keys only after PRAGMA foreign_keys = ON; on that connection. Other databases enforce them always.

Summary

  • A relational database stores data in tables; each table has fixed columns and any number of rows, with no built-in order.
  • A primary key uniquely identifies every row. Surrogate keys (like id) never need to change; natural keys come from real data.
  • A foreign key stores another table's primary key and links the two tables. Many-to-many relationships need a junction table.
  • SQLite only enforces foreign keys after PRAGMA foreign_keys = ON;.
  • The schema is the database's structure. In SQLite, read it with sqlite_schema, PRAGMA table_info and PRAGMA foreign_key_list; other databases offer information_schema.

Next: SQL Dialects and SQLite, where you'll see how SQLite compares with PostgreSQL, MySQL and SQL Server.