Tell me about a 1:many (one-to-many) relationship

SQLJunior~1 min read

A one-to-many (1:N) relationship is the most common type of table relationship. It means one record in the first table can relate to several records in the second, but each record in the second table belongs to only one record in the first.

Example

One user -> many orders

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Here:

users.id is the primary key (the user's unique identifier),
orders.user_id is a foreign key showing which user the order belongs to.

How it looks logically

users	orders
id = 1, name = 'Anna'	id = 101, user_id = 1, amount = 500
	id = 102, user_id = 1, amount = 900
id = 2, name = 'Ivan'	id = 103, user_id = 2, amount = 200

Anna has two orders, Ivan has one, but each order can belong to only one user.

Example query

To join the data and see all orders with customer names:

sql

SELECT users.name, orders.amount
FROM users
JOIN orders ON users.id = orders.user_id;

Result:

name	amount
Anna	500
Anna	900
Ivan	200

Where 1:N relationships are used

One customer -> many orders
One instructor -> many students
One department -> many employees
One author -> many books

Details

The parent table (the "one" side) holds the primary key.
The child table (the "many" side) holds the foreign key pointing to the "one".
If a parent record is deleted, related rows can become "orphaned", this is controlled by settings like ON DELETE CASCADE, SET NULL, and similar.

Summary: A 1:N (one-to-many) relationship links a parent table to many dependent records. It's used when one object can have several dependents, and is implemented via a foreign key on the "many" table.

Short Answer

Interview ready

Premium

A concise answer to help you respond confidently on this topic during an interview.

Tell me about a 1:many (one-to-many) relationship

#Example

#How it looks logically

#Example query

#Where 1:N relationships are used

#Details