Relational basics (tables, rows, columns)

Tables, rows and columns

A table is a named collection of rows that all share the same set of columns. Each row is one record, such as one customer or one order. Each column is a named attribute with a data type, shared by every row. The schema describes the structure: tables, columns, data types and constraints.

A query without ORDER BY gives no guarantee about row order, even if it looks stable today.

Example

Tables, rows and columns

CREATE TABLE users (
  id    INTEGER PRIMARY KEY,   -- unique, never NULL
  name  TEXT,
  email TEXT NOT NULL UNIQUE,  -- must be given, no duplicates
  age   INTEGER,               -- whole numbers
  balance NUMERIC(10, 2)       -- exact decimals, e.g. money
);

Keys and constraints

A primary key uniquely identifies each row and cannot be NULL. A good key never changes, so a generated integer id beats an email or name. A composite primary key spans several columns, and only the combination must be unique, so (1, 10) and (1, 11) can coexist but (1, 10) twice cannot.

A foreign key makes a column reference another table's key, so an order cannot point to a customer that does not exist. NOT NULL forces a value (unless a DEFAULT supplies one), and UNIQUE forbids duplicates. Violating any constraint makes the statement fail. This enforced structure is the main reason to choose a relational database over a flexible document store.

Data types

Pick the type that matches the data: INTEGER for whole numbers like age, TEXT for strings, and NUMERIC(10, 2) for prices where exact decimal arithmetic matters. Floating-point types can introduce rounding errors, so avoid them for money.

NULL

NULL means a missing or unknown value. It is not zero and not an empty string. Comparisons with NULL are neither true nor false but unknown, so x = NULL and x <> 1 never match NULL rows; use IS NULL instead. COUNT(*) counts rows while COUNT(x) skips NULLs, and AVG ignores NULLs too.

NOT IN against a subquery that returns any NULL yields no rows. In PostgreSQL, UNIQUE allows multiple NULLs because NULLs are not considered equal. A column DEFAULT applies only when the column is omitted; explicitly inserting NULL stores NULL.

Example

NULL

SELECT * FROM users WHERE age = NULL;     -- returns nothing
SELECT * FROM users WHERE age IS NULL;    -- correct
SELECT COUNT(*), COUNT(age) FROM users;  -- COUNT(age) skips NULLs
SELECT AVG(COALESCE(age, 0)) FROM users; -- treat NULL as 0

SQL categories and ACID

DDL (Data Definition Language) defines structure, for example CREATE TABLE. DML (Data Manipulation Language) changes rows: INSERT, UPDATE, DELETE.

A transaction groups statements and gives ACID guarantees. Atomicity: all or nothing, so a failed transfer is rolled back. Consistency: constraints hold before and after. Isolation: concurrent transactions do not interfere. Durability: committed data survives a crash. In PostgreSQL even DDL can be rolled back inside a transaction.