Relational databases, refreshed

The relational model in plain words

A DBMS gives a business what a file cannot: shared access, concurrency, durability and enforced rules (see Transactions and concurrency). Codd's 1970 model gave it this vocabulary, formal word then everyday one.

Relation (table)

A named set of rows with the same columns.

Tuple (row, record)

One entry: (1, 'Amira', 'Dubai').

Attribute (column, field)

A named, typed slot every row has (types).

Schema

The structure: tables, columns, types, keys, constraints. It changes only in a migration.

Three rules still matter. One value per cell, so a film's five lead actors never go in one cast column, they go in a table of their own (chapter 3 builds it). Rows have no inherent order, so only ORDER BY guarantees one. Rows are identified by their values, hence keys.

Both engines expose the schema through INFORMATION_SCHEMA views; PostgreSQL also has pg_catalog.

Keys: how rows are identified and connected

A key is a column, or group of columns, whose values identify a row.

Primary, candidate, natural and surrogate

• Primary key. Unique, never NULL, at most one per table: customers.id.
• Candidate key. Any other set that could have served, such as a unique email. Declare those UNIQUE.
• Natural versus surrogate. An email or ISBN comes from the business and can change; a handed-out number does not. MySQL uses AUTO_INCREMENT, PostgreSQL an identity column.
• Composite key. Spans columns: order_items is keyed by (order_id, product_id).

Foreign keys and referential integrity

A foreign key holds another table's primary key value. The engine then refuses a child row with no parent, and refuses to delete a parent with children unless you named an action: RESTRICT or NO ACTION (refuse, the default), CASCADE (delete the children) or SET NULL (blank the pointer).

CREATE TABLE orders (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NULL,
  CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id)
    REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB;
CREATE TABLE order_items (
  PRIMARY KEY (order_id, product_id),
  CONSTRAINT fk_items_order   FOREIGN KEY (order_id)   REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(id)
);

Delete Amira and orders 10 and 11 stay with customer_id NULL, like guest order 13. Delete order 10 and its order_items rows go too.

INSERT INTO orders (customer_id, order_date) VALUES (99, '2026-04-01');
-- MySQL:      ERROR 1452 (23000): Cannot add or update a child row:
--             a foreign key constraint fails (...fk_orders_customer...)
-- PostgreSQL: ERROR: insert or update on table "orders" violates
--             foreign key constraint "fk_orders_customer"  (SQLSTATE 23503)

DELETE FROM products WHERE id = 101;
-- MySQL:      ERROR 1451 (23000): Cannot delete or update a parent row:
--             a foreign key constraint fails (...fk_items_product...)
-- PostgreSQL: ERROR: update or delete on table "products" violates
--             foreign key constraint "fk_items_product" on table "order_items"

Those errors are good news. The same family covers UNIQUE (MySQL 1062, PostgreSQL SQLSTATE 23505), NOT NULL and CHECK, enforced in MySQL since 8.0.16. More: Designing good tables.

Relationships at a glance

There are only three shapes, and the shape says where the foreign key goes.

Normalization is in Designing good tables; putting the tables back together is Joins.

NULL: the value that is not a value

NULL marks the absence of a value, not zero and not an empty string. Chloé's city is NULL because it is unknown; order 13's customer_id is NULL because a guest checkout has no customer.

SQL uses three-valued logic: any comparison with NULL is UNKNOWN, even NULL = NULL. WHERE keeps only rows that are TRUE, so city = NULL keeps nothing and city <> 'Dubai' silently drops Chloé. Test with IS NULL.

SELECT name FROM customers WHERE city = NULL;    -- 0 rows, always
SELECT name FROM customers WHERE city IS NULL;   -- Chloé

-- Count rows, versus count non-NULL values in a column
SELECT COUNT(*) AS rows_total, COUNT(city) AS cities_known
FROM customers;

COUNT(*) counts rows; COUNT(city) counts values, and NULL is not one. SUM, AVG, MIN and MAX ignore NULLs too (see Aggregation and window functions). For NULL-safe equality, MySQL has <=> and PostgreSQL IS NOT DISTINCT FROM; both have COALESCE().

The words that trip returning people

A server is the machine or the program on it, so ask which. An instance is one running copy, with its own configuration file, data directory and port.

So "a separate schema" is a namespace inside one database in PostgreSQL, and a separate database in MySQL. PostgreSQL resolves unqualified names through search_path, and since version 15 creating in public needs a grant (Users, roles and security).

Client and server

The server is one process (mysqld.exe or postgres.exe) running as a Windows service on a TCP port; a client sends SQL text and gets rows back. See Clients and tools and The SQL map.

# The server is just a Windows service; names come from the installer
Get-Service MySQL84, postgresql-x64-18

# Who is listening on the MySQL port?
netstat -ano | findstr 3306

What a DBA owns, and what developers own

Developers own the queries and most of the schema design. You review and protect.

Each row has its own chapter, from Users, roles and security to A DBA's working week. The posture: ask for the foreign keys and the rollback plan before saying yes.

Going deeper

• MySQL 8.4: FOREIGN KEY constraints
• PostgreSQL: Constraints
• MySQL 8.4: Working with NULL values
• MySQL 8.4: CREATE DATABASE
• PostgreSQL: Schemas