PostgreSQL.

ACID names four guarantees a transaction gives. PostgreSQL provides them with constraints, multiversion concurrency control (MVCC) snapshots and its write-ahead log (WAL).

• Atomicity means a transaction is all or nothing. If any statement in it fails, PostgreSQL rolls the whole transaction back, and none of its changes ever become visible.
• Consistency means every transaction leaves the data valid. PostgreSQL enforces this with constraints such as primary keys, foreign keys and CHECK.
• Isolation means a transaction does not see the unfinished changes of others. PostgreSQL gives each statement an MVCC snapshot of the data.
• Durability means a committed change survives a crash. PostgreSQL records the change in the WAL and flushes that log to disk before COMMIT returns.

Outside a BEGIN block, PostgreSQL runs each statement as its own transaction. Wrapping the transfer below in BEGIN and COMMIT makes its two updates one atomic unit.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- Both operations succeed or both fail

PostgreSQL's main types cover numbers, text, and dates and times. It also stores binary data and JSON. Arrays, UUIDs and network addresses have their own types too.

• Whole numbers use integer or bigint. Exact decimals use numeric, also spelled decimal, and approximate values use real or double precision.
• Text uses char, varchar or text. Unlike in some other database systems, char has no speed advantage in PostgreSQL.
• Dates and times use date, time and timestamp, and spans of time use interval.
• boolean holds true or false, and bytea holds binary data.
• json keeps JSON as plain text, while jsonb stores it in a binary form that is faster to query.
• Arrays can hold any data type, uuid stores universally unique identifiers, and inet and cidr store IP addresses.

Both reject duplicate values, but a primary key also forbids NULLs, and a table can have only one. A table can have many UNIQUE constraints, and their columns accept NULLs.

• A primary key is the row's main identifier, and foreign keys usually point to it. PostgreSQL requires its values to be unique and not null.
• A UNIQUE column accepts many NULLs by default, because two NULLs never count as equal. From PostgreSQL 15, declaring it UNIQUE NULLS NOT DISTINCT allows only one NULL.
• Both create a unique B-tree index automatically, and a foreign key can reference either one.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,        -- Cannot be NULL
    email VARCHAR(255) UNIQUE,    -- Can have many NULLs
    username VARCHAR(50) UNIQUE   -- Multiple unique constraints allowed
);

DELETE removes chosen rows, TRUNCATE empties the whole table, and DROP removes the table itself.

• DELETE removes the rows its WHERE clause matches and fires ON DELETE triggers. The deleted rows stay on disk as dead rows until VACUUM reclaims the space.
• TRUNCATE removes every row without scanning the table, so it is faster and frees the disk space at once. It fires ON TRUNCATE triggers, not ON DELETE triggers, and it blocks all other access to the table.
• DROP removes the table definition along with its data, indexes and constraints.

In PostgreSQL, all three can be rolled back inside a transaction, because schema changes such as DROP TABLE are transactional too. A ROLLBACK after BEGIN brings a dropped table back.

DELETE FROM users WHERE age < 18;  -- Removes specific rows
TRUNCATE TABLE users;              -- Removes all rows
DROP TABLE users;                  -- Removes table entirely

A foreign key is a column whose values must match a row in another table. PostgreSQL rejects any change that would break that link, which is called referential integrity.

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(100),
    dept_id INTEGER REFERENCES departments(dept_id)
);

Here every employees.dept_id must name an existing department or be NULL. PostgreSQL enforces the link in three ways.

• An INSERT or UPDATE that sets a dept_id with no matching department fails.
• Deleting a department that employees still reference fails by default, which is ON DELETE NO ACTION.
• ON DELETE CASCADE deletes the referencing employees instead, and ON DELETE SET NULL sets their dept_id to NULL.

The referenced column must be a primary key or have a unique constraint. PostgreSQL does not index the referencing column for you, so a delete from departments must search employees unless you index employees.dept_id.