Relational model and your setup

What a relational database is, the keys that connect tables, and the server, client and sample data you need before writing real queries.

Relational model

Also called table, row, column, relation, RDBMS, relational database

What: Data stored in tables: each table describes one kind of thing, each row is one instance of it and each column is one attribute with a fixed type. Tables are linked through shared key values rather than pointers.
Why: Because every fact has one obvious home, you can ask new questions of old data with SQL without changing how it is stored. That flexibility is why relational databases still run most business software.
Example: A shop has a customers table, a products table and an orders table. An order row stores customer_id 42 instead of copying the customer name, and a query joins them back together.

Watch out

Treating a table like a spreadsheet tab and packing several facts, such as a comma-separated list of products, into one cell.

Primary key

Also called PK

What: A column, or set of columns, whose value is unique and never NULL for every row, so it identifies exactly one row. A table has one primary key.
Why: Updates, deletes and relationships all need a reliable way to point at one row. The database also builds a unique index on it, so lookups by key are fast.

Watch out

Using something that looks unique but can change or repeat, such as an email address or a person's name.

Foreign key

Also called FK, referential integrity

What: A column that stores the primary key of a row in another table, declared with a constraint so the database rejects values that do not exist there.
Why: It is how tables relate, and the constraint stops orphaned rows, such as an order for a customer who was never created or has been deleted.

SQL

CREATE TABLE orders (
  id          int PRIMARY KEY,
  customer_id int NOT NULL
    REFERENCES customers (id),
  placed_at   date NOT NULL
);

Watch out

Leaving the constraint off "for speed" and later finding thousands of rows that point at nothing.

DDL, DML and DCL

Also called data definition language, data manipulation language, data control language, TCL, DDL, DML, DCL

What: The families of SQL statements: DDL defines structure (CREATE, ALTER, DROP), DML reads and changes rows (SELECT, INSERT, UPDATE, DELETE), DCL grants access (GRANT, REVOKE) and transaction control groups changes (COMMIT, ROLLBACK).
Why: Knowing which family a statement belongs to tells you its risk. A wrong SELECT wastes a second; a wrong DROP or UPDATE without WHERE can lose data.

Watch out

Assuming DDL can always be rolled back: MySQL commits implicitly on most DDL, while PostgreSQL and SQL Server can roll it back inside a transaction.

Command-line client

Also called psql, mysql client, sqlcmd, CLI

What: A terminal program that connects to the database server and runs SQL you type or pipe in: psql for PostgreSQL, mysql for MySQL, sqlcmd for SQL Server.
Why: Servers, containers and CI pipelines rarely have a GUI. Running scripts, restoring dumps and checking a production issue all happen in the terminal.

SQL

psql -h localhost -U postgres -d shop
\dt             -- list tables
\d orders       -- describe a table
\i seed.sql     -- run a script
\q              -- quit

Watch out

In sqlcmd, forgetting that nothing runs until you type GO on its own line.

pgAdmin

What: The open source graphical tool for PostgreSQL: an object browser, a query tool with history and a graphical EXPLAIN, and backup and restore screens. It runs as a desktop app or in a browser.
Why: Browsing tables, reading a query plan as a diagram and editing a few rows are faster with a GUI while you learn, as long as you still learn psql alongside it.

Watch out

Clicking through dialogs without reading the SQL pgAdmin generates, so you never learn the statements behind them.

Sample database

Also called pagila, sakila, AdventureWorks, WideWorldImporters

What: A ready-made database with realistic tables and thousands of rows that you load locally to practise on, such as pagila for PostgreSQL, sakila for MySQL or AdventureWorks for SQL Server.
Why: Toy tables with five rows hide the problems that matter: duplicates, NULLs, joins that multiply rows and queries that are slow at scale.

SQL

createdb pagila
psql -d pagila -f pagila-schema.sql
psql -d pagila -f pagila-data.sql

Go deeper