Tables, Rows, Columns, and Relationships

A table represents a kind of entity, such as customers or orders. Each row represents one record, and each column represents an attribute with a declared type. A primary key uniquely identifies a row. A foreign key links a value in one table to a key in another table, allowing the database to enforce referential integrity.

Relational design encourages storing each fact in an appropriate place and representing relationships explicitly. For example, an order should usually reference a customer rather than repeat all customer details. This reduces duplication and makes updates more reliable.

Customer and order relationship

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  total NUMERIC(12, 2) NOT NULL CHECK (total >= 0)
);

💡 The order's customer_id references a real customer. The primary keys uniquely identify rows, while NOT NULL, UNIQUE, and CHECK constraints protect basic data rules.

Key Takeaways

A primary key identifies a row.
A foreign key enforces a relationship.
Use constraints to make important data rules enforceable by the database.

Knowledge Check

Which constraint enforces that an order references an existing customer?

Tables, Rows, Columns, and Relationships

STAY UPDATED WITH BYTES!