Designing Tables with Keys and Relationships

Why Keys and Relationships Matter in Table Design

When you design tables, you are deciding how data will be stored, connected, and protected from inconsistencies. Keys and relationships are the main tools for doing this. A key is a rule that identifies rows and enforces uniqueness. A relationship is a rule that connects rows across tables and enforces that the connection is valid. Together, they help you avoid duplicate data, prevent “orphan” records (rows that refer to something that does not exist), and make queries predictable.

This chapter focuses on practical table design using primary keys, foreign keys, and related constraints. The goal is to design tables that represent real-world entities (like customers, orders, and products) and the links between them (like “an order belongs to a customer”).

Primary Keys: The Row Identity

A primary key (PK) is a column (or set of columns) that uniquely identifies each row in a table. A good primary key is stable (doesn’t change), unique, and always present (not NULL). Most databases enforce these rules automatically when you declare a primary key.

Choosing a Primary Key: Natural vs Surrogate

There are two common approaches:

• Natural key: a real-world identifier already present in the data, such as a national ID number, an ISBN for books, or an email address.

  Continue in our app.

  You can listen to the audiobook with the screen off, receive a free certificate for this course, and also have access to 5,000 other free online courses.

  Or continue reading below...

  

  Download the app

• Surrogate key: an artificial identifier created solely for the database, often an integer generated automatically (IDENTITY, SERIAL, AUTO_INCREMENT) or a UUID.

Natural keys can be meaningful, but they can also change (emails change), may not be truly unique in practice, or may be too long to use efficiently in relationships. Surrogate keys are stable and compact, but you usually still need additional constraints to enforce real-world uniqueness (for example, a UNIQUE constraint on email).

Example: Customers Table with a Surrogate Primary Key

CREATE TABLE customers (  customer_id INTEGER PRIMARY KEY,  full_name   VARCHAR(200) NOT NULL,  email       VARCHAR(320) NOT NULL,  created_at  TIMESTAMP NOT NULL);

In many databases, you would configure customer_id to auto-generate. The exact syntax varies, but the design idea is the same: a single-column primary key that never changes.

Composite Primary Keys

A composite primary key uses multiple columns together to form uniqueness. This is common in “link tables” (also called junction tables) that represent many-to-many relationships.

Example: if a student can enroll in many courses and each course can have many students, the enrollment table might use (student_id, course_id) as its primary key.

CREATE TABLE enrollments (  student_id INTEGER NOT NULL,  course_id   INTEGER NOT NULL,  enrolled_on DATE NOT NULL,  PRIMARY KEY (student_id, course_id));

Composite keys can be perfectly valid, but they can make foreign keys and joins more verbose because every reference must include all key columns. Many designs use a surrogate key for the link table and add a UNIQUE constraint on the pair to preserve the business rule.

Unique Constraints: Enforcing Real-World Uniqueness

A primary key guarantees uniqueness for row identity, but you often need additional uniqueness rules. A UNIQUE constraint prevents duplicates in a column or a set of columns.

Example: customers should not share the same email address.

CREATE TABLE customers (  customer_id INTEGER PRIMARY KEY,  full_name   VARCHAR(200) NOT NULL,  email       VARCHAR(320) NOT NULL,  created_at  TIMESTAMP NOT NULL,  CONSTRAINT uq_customers_email UNIQUE (email));

UNIQUE constraints are also important for relationships. A foreign key can reference either a primary key or a column (or columns) that have a UNIQUE constraint. This is useful when you want to reference a natural identifier while still using a surrogate primary key internally.

Foreign Keys: Connecting Tables Safely

A foreign key (FK) is a constraint that ensures values in one table correspond to existing values in another table. It enforces referential integrity: you cannot reference a parent row that does not exist.

Common pattern: an orders table references customers. Each order belongs to exactly one customer, so orders.customer_id is a foreign key to customers.customer_id.

CREATE TABLE orders (  order_id     INTEGER PRIMARY KEY,  customer_id  INTEGER NOT NULL,  order_date   DATE NOT NULL,  status       VARCHAR(30) NOT NULL,  CONSTRAINT fk_orders_customer  FOREIGN KEY (customer_id) REFERENCES customers(customer_id));

This design prevents inserting an order for a customer that does not exist. It also helps the database optimizer understand relationships, which can improve query planning.

Foreign Key Columns: Data Type and Nullability

To design foreign keys correctly:

• Match data types: the FK column should have the same type and size as the referenced key. If customers.customer_id is INTEGER, orders.customer_id should also be INTEGER.

• Decide whether NULL is allowed: if every order must have a customer, use NOT NULL. If the relationship is optional (for example, a support ticket might be created without an assigned agent), allow NULL.

• Indexing: many databases do not automatically index foreign key columns. Indexing FKs often improves join performance and speeds up deletes/updates on parent rows (because the database must check child rows). The exact index syntax varies, but the design principle is consistent.

Relationship Types and How They Affect Table Design

One-to-Many (1:N)

One customer can have many orders. You implement this by placing the foreign key on the “many” side (orders).

-- customers (1) ----< orders (many)

Key design decisions:

• Use a primary key on each table.

• Put the FK in the child table.

• Choose whether the FK is mandatory (NOT NULL) or optional.

One-to-One (1:1)

One-to-one relationships are less common, but they appear when you split a table for security, optional data, or performance reasons. Example: a users table and a user_profiles table where each user has at most one profile.

To enforce one-to-one, the foreign key must also be unique (or be the primary key) in the dependent table.

CREATE TABLE users (  user_id   INTEGER PRIMARY KEY,  username  VARCHAR(50) NOT NULL,  CONSTRAINT uq_users_username UNIQUE (username));  CREATE TABLE user_profiles (  user_id      INTEGER PRIMARY KEY,  display_name VARCHAR(100) NOT NULL,  bio          VARCHAR(500),  CONSTRAINT fk_profiles_user  FOREIGN KEY (user_id) REFERENCES users(user_id));

Here, user_profiles.user_id is both the primary key and a foreign key. That guarantees there can be at most one profile row per user and that every profile belongs to a real user.

Many-to-Many (M:N)

Many-to-many relationships require a link table. Example: products and orders. An order can contain many products, and a product can appear in many orders. You represent this with an order_items table.

CREATE TABLE products (  product_id   INTEGER PRIMARY KEY,  product_name VARCHAR(200) NOT NULL,  unit_price   DECIMAL(10,2) NOT NULL);  CREATE TABLE order_items (  order_id   INTEGER NOT NULL,  product_id INTEGER NOT NULL,  quantity   INTEGER NOT NULL,  unit_price DECIMAL(10,2) NOT NULL,  PRIMARY KEY (order_id, product_id),  CONSTRAINT fk_items_order   FOREIGN KEY (order_id) REFERENCES orders(order_id),  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(product_id));

The composite primary key (order_id, product_id) ensures that the same product cannot be listed twice in the same order. If you need to allow duplicates (for example, the same product at different discounts), you would add an additional column (like line_number) and include it in the key, or use a surrogate key and enforce uniqueness with a separate constraint.

Referential Actions: What Happens on Update/Delete

Foreign keys can specify what should happen when a referenced parent row is updated or deleted. These are called referential actions. The most common are:

• RESTRICT / NO ACTION: prevent the delete/update if child rows exist.

• CASCADE: automatically delete/update child rows when the parent changes.

• SET NULL: set the foreign key in child rows to NULL when the parent is deleted/updated (requires FK column to allow NULL).

• SET DEFAULT: set the foreign key to a default value (less common; requires a meaningful default).

Example: deleting a customer might be restricted if orders exist, because you may need to keep order history. But deleting an order might cascade to its order items, because order items have no meaning without the order.

CREATE TABLE orders (  order_id     INTEGER PRIMARY KEY,  customer_id  INTEGER NOT NULL,  order_date   DATE NOT NULL,  status       VARCHAR(30) NOT NULL,  CONSTRAINT fk_orders_customer  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)  ON DELETE RESTRICT);  CREATE TABLE order_items (  order_id   INTEGER NOT NULL,  product_id INTEGER NOT NULL,  quantity   INTEGER NOT NULL,  unit_price DECIMAL(10,2) NOT NULL,  PRIMARY KEY (order_id, product_id),  CONSTRAINT fk_items_order  FOREIGN KEY (order_id) REFERENCES orders(order_id)  ON DELETE CASCADE,  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(product_id));

Design tip: use CASCADE carefully. It can be convenient, but it can also delete large amounts of data if used on the wrong relationship. A common safe pattern is CASCADE from parent to purely dependent child tables (like order to order_items), and RESTRICT for business-critical history (like customer to orders).

Step-by-Step: Designing a Small Schema with Keys and Relationships

Let’s design a simple online shop schema focusing on keys and relationships. We will build: customers, orders, products, and order_items.

Step 1: Identify Entities and Their Identifiers

• customers: identify each customer with customer_id (surrogate PK). Enforce unique email.

• products: identify each product with product_id (surrogate PK). Optionally enforce unique product_name or SKU if you have one.

• orders: identify each order with order_id (surrogate PK). Each order belongs to one customer.

• order_items: link orders and products. Each row is one product line in an order.

Step 2: Create Parent Tables First

Create tables that do not depend on others: customers and products.

CREATE TABLE customers (  customer_id INTEGER PRIMARY KEY,  full_name   VARCHAR(200) NOT NULL,  email       VARCHAR(320) NOT NULL,  created_at  TIMESTAMP NOT NULL,  CONSTRAINT uq_customers_email UNIQUE (email));  CREATE TABLE products (  product_id   INTEGER PRIMARY KEY,  product_name VARCHAR(200) NOT NULL,  unit_price   DECIMAL(10,2) NOT NULL,  CONSTRAINT chk_products_price CHECK (unit_price >= 0));

Note the CHECK constraint on price. While not a key, it complements key design by preventing invalid values that would otherwise spread through relationships.

Step 3: Create the Child Table for the 1:N Relationship

Orders depend on customers, so create orders with a foreign key to customers.

CREATE TABLE orders (  order_id     INTEGER PRIMARY KEY,  customer_id  INTEGER NOT NULL,  order_date   DATE NOT NULL,  status       VARCHAR(30) NOT NULL,  CONSTRAINT fk_orders_customer  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)  ON DELETE RESTRICT);

Here, ON DELETE RESTRICT expresses a business rule: you cannot delete a customer if they have orders. If your business allows deleting customers but keeping orders, you might instead store customer details on the order (a snapshot) and allow deletion, or use a “soft delete” flag on customers.

Step 4: Create the Link Table for the M:N Relationship

Order items depend on orders and products. Create order_items with two foreign keys and a composite primary key.

CREATE TABLE order_items (  order_id   INTEGER NOT NULL,  product_id INTEGER NOT NULL,  quantity   INTEGER NOT NULL,  unit_price DECIMAL(10,2) NOT NULL,  PRIMARY KEY (order_id, product_id),  CONSTRAINT fk_items_order  FOREIGN KEY (order_id) REFERENCES orders(order_id)  ON DELETE CASCADE,  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products(product_id),  CONSTRAINT chk_items_quantity CHECK (quantity > 0),  CONSTRAINT chk_items_price CHECK (unit_price >= 0));

Notice unit_price is stored in order_items even though products has unit_price. This is a design choice: it captures the price at the time of purchase, which might differ from the current product price later. Keys and relationships do not prevent you from storing derived or historical values; they help ensure the references remain valid.

Step 5: Test the Constraints with Sample Inserts

Try inserting valid rows first:

INSERT INTO customers (customer_id, full_name, email, created_at) VALUES (1, 'Ava Chen', 'ava@example.com', CURRENT_TIMESTAMP);  INSERT INTO products (product_id, product_name, unit_price) VALUES (10, 'Coffee Beans 1kg', 18.50);  INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (100, 1, CURRENT_DATE, 'NEW');  INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (100, 10, 2, 18.50);

Now try violating referential integrity by inserting an order for a non-existent customer:

INSERT INTO orders (order_id, customer_id, order_date, status) VALUES (101, 999, CURRENT_DATE, 'NEW');

The database should reject this because customer 999 does not exist. That is the foreign key doing its job.

Try violating uniqueness by inserting a second customer with the same email:

INSERT INTO customers (customer_id, full_name, email, created_at) VALUES (2, 'Ava C.', 'ava@example.com', CURRENT_TIMESTAMP);

The database should reject this because of uq_customers_email.

Common Design Mistakes and How Keys Help Prevent Them

Using Meaningful Data as a Primary Key Without Considering Change

Example: using email as the primary key. If a user changes their email, every referencing table must update too. With a surrogate key, the email can change without affecting relationships. If you still need email uniqueness, enforce it with a UNIQUE constraint.

Missing Foreign Keys

It is possible to create tables without foreign keys and rely on application code to keep references valid. This often leads to orphan rows and inconsistent data over time. Declaring foreign keys makes the database enforce the rules consistently, regardless of which application or script writes data.

Incorrect Cardinality

Sometimes a relationship that is actually many-to-many is modeled as one-to-many, leading to repeated columns or limited functionality. If an order can contain multiple products, storing product_id directly on orders forces you into one product per order or encourages repeating columns like product_id_1, product_id_2. A link table with keys solves this cleanly.

Not Enforcing “At Most One” Rules

If you need one-to-one, you must enforce it. A foreign key alone does not guarantee one-to-one; it only guarantees the referenced parent exists. Add a UNIQUE constraint or make the FK also the PK in the dependent table.

Advanced Key Patterns You Will See Often

Alternate Keys (Candidate Keys)

An alternate key is a column (or set of columns) that could uniquely identify a row but is not chosen as the primary key. In SQL, you enforce it with a UNIQUE constraint. Example: customers.email is an alternate key when customer_id is the primary key.

Self-Referencing Foreign Keys (Hierarchies)

Some tables reference themselves to represent hierarchies, such as categories with parent categories, or employees with managers.

CREATE TABLE categories (  category_id   INTEGER PRIMARY KEY,  category_name VARCHAR(200) NOT NULL,  parent_category_id INTEGER,  CONSTRAINT fk_categories_parent  FOREIGN KEY (parent_category_id) REFERENCES categories(category_id));

This allows a category to have a parent category. The FK is nullable because top-level categories have no parent.

Deferrable Constraints (When Insert Order Is Hard)

In some databases, you can defer foreign key checks until the end of a transaction. This helps when inserting mutually dependent rows. Not all databases support this feature, but the design idea is worth knowing: constraints can sometimes be checked later, while still being enforced.

Practical Checklist for Designing Tables with Keys and Relationships

• Choose a primary key for every table. Prefer stable, non-changing identifiers.

• Use UNIQUE constraints for real-world uniqueness (email, SKU, username) even if you use surrogate primary keys.

• Implement one-to-many by placing the foreign key on the many side.

• Implement many-to-many with a link table and enforce uniqueness with a composite primary key or a UNIQUE constraint.

• For one-to-one, make the foreign key unique (often by making it the primary key).

• Decide referential actions (CASCADE/RESTRICT/SET NULL) based on business meaning and data retention needs.

• Ensure foreign key columns match the referenced key’s data type and nullability rules.

• Consider indexing foreign key columns for performance in joins and deletes/updates.