This lesson's interactive features are locked, please to keep using them

One to Many

When talking about the relationships between tables, a one-to-many relationship is probably the most commonly used relationship.

A one-to-many relationship occurs when a single record in one table is related to potentially many records in another table.

The one → many relation only goes one way; a record in the second table cannot be related to multiple records in the first table!

Examples

A customers table and an orders table. Each customer has 0, 1, or many orders that they've placed.
A users table and a transactions table. Each user has 0, 1, or many transactions that they've taken part in.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  amount REAL NOT NULL,
  customer_id INTEGER,
  CONSTRAINT fk_customers
    FOREIGN KEY (customer_id)
    REFERENCES customers(id)
);

Assignment

It's important that we track which devices our users are using to log into CashPal for security purposes. A user can log in from potentially many devices (e.g., a phone, a tablet, and a laptop), but each specific device is owned by a single user.

Let's create a one-to-many relationship between users and their devices.

Create a new table called devices with 4 fields:
- id: an integer primary key
- mac_address: TEXT
- type: TEXT
- user_id: an integer foreign key to the id field of the users table