One-to-one and one-to-many relationships
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)Every line in an ER diagram is a relationship, and the first thing to settle about it is how many rows can sit on each side. That one answer decides which table gets the foreign key and whether it must be unique.
Cardinality: how many on each side
Cardinality is the largest number of rows on each side of a relationship: one-to-one (1:1), one-to-many (1:N) or many-to-many (M:N). Ask two questions, one from each side, and answer each with one or many.
For every relationship, ask two questions, one from each side. The picture shows the three possible answers; the text below walks through the questions for a train and its coaches.
Example 1 · Two questions for every relationship
Pair Question from the left Question from the right Cardinality----------------------- --------------------------- --------------------------- -----------person - Aadhaar number One person has how many? One number is for how many? 1:1 -> one -> onetrain - coach One train has how many? One coach is in how many? 1:N -> many -> onecustomer - order One customer places? One order is placed by? 1:N -> many -> onestudent - course One student takes? One course is taken by? M:N -> many -> manyone + one -> one-to-one (foreign key on either side, made UNIQUE) many + one -> one-to-many (foreign key on the many side) many + many -> many-to-many (needs a third table: next lesson)
What this code does
- Each pair gets one question from each side, answered only with "one" or "many".
- Student and course look like one-to-many if you ask only the first question. The second question reveals many-to-many.
For a train and its coaches: "One train has how many coaches?" Many. "One coach belongs to how many trains?" One. One answer "many" and one answer "one" makes a one-to-many relationship.
Always ask both questions. People often ask only the first, see "many" and stop, and then miss a many-to-many relationship that needs a different design (the next lesson).
One-to-many with a foreign key on the many side
In a one-to-many relationship, one parent row links to many child rows. The foreign key goes in the many side, as one column holding the parent's key, so the child points to its parent.
A train has many coaches, so each coach row should store its train. First see the tempting wrong design, a comma list of coaches inside the train row; the right design follows in the scripts.
CREATE TABLE trains_v1 ( train_no CHAR(5) PRIMARY KEY, name VARCHAR(40) NOT NULL, coaches VARCHAR(200) NOT NULL -- many values in one cell);INSERT INTO trains_v1 VALUES ('12627', 'Karnataka Express', 'S1,S2,B3'), ('12951', 'Mumbai Rajdhani', 'A1,B30');
-- Which train has coach B3? Only one does.SELECT train_no, nameFROM trains_v1WHERE coaches LIKE '%B3%'ORDER BY train_no;+----------+-------------------+ | train_no | name | +----------+-------------------+ | 12627 | Karnataka Express | | 12951 | Mumbai Rajdhani | +----------+-------------------+
The tempting mistake is the other way round: storing the children inside the parent, as a comma-separated list or as columns coach1, coach2, coach3. The snippet shows why that breaks. A search with LIKE '%B3%' also finds B30, a train with a fourth coach needs a new column, and no foreign key can check the values.
Example 2 · ER diagram: one train, many coaches
+----------------+ +----------------+| trains | | coaches ||----------------| |----------------|| PK train_no | ||------|{ | PK coach_id || name | | FK train_no |+----------------+ | code | | coach_type | | berths | +----------------+One train has one or many coaches. Each coach belongs to exactly one train. The foreign key train_no sits in coaches, the many side.
What this code does
|{next to coaches: a train has at least one coach.||next to trains: a coach is in exactly one train.FK train_nois inside the coaches box, which is where the foreign key of a one-to-many always goes.
Example 3 · One-to-many done right
CREATE TABLE trains ( train_no CHAR(5) PRIMARY KEY, name VARCHAR(40) NOT NULL);
CREATE TABLE coaches ( coach_id INT AUTO_INCREMENT PRIMARY KEY, train_no CHAR(5) NOT NULL, -- the parent's key code VARCHAR(4) NOT NULL, -- S1, B3, A1 ... coach_type ENUM('SL', '3A', '2A') NOT NULL, berths TINYINT NOT NULL, UNIQUE (train_no, code), -- no two B3s in one train FOREIGN KEY (train_no) REFERENCES trains (train_no));
INSERT INTO trains VALUES ('12627', 'Karnataka Express'), ('12951', 'Mumbai Rajdhani');INSERT INTO coaches (train_no, code, coach_type, berths) VALUES ('12627', 'S1', 'SL', 72), ('12627', 'S2', 'SL', 72), ('12627', 'B3', '3A', 64), ('12951', 'A1', '2A', 48), ('12951', 'B30', '3A', 64);
-- Which train has coach B3? An exact match now.SELECT t.train_no, t.nameFROM trains tJOIN coaches c ON c.train_no = t.train_noWHERE c.code = 'B3';
-- Coaches and berths per trainSELECT t.name, COUNT(*) AS coaches, SUM(c.berths) AS berthsFROM trains tJOIN coaches c ON c.train_no = t.train_noGROUP BY t.train_no, t.nameORDER BY t.name;+----------+-------------------+ | train_no | name | +----------+-------------------+ | 12627 | Karnataka Express | +----------+-------------------+ +-------------------+---------+--------+ | name | coaches | berths | +-------------------+---------+--------+ | Karnataka Express | 3 | 208 | | Mumbai Rajdhani | 2 | 112 | +-------------------+---------+--------+
What this code does
- Each coach is a row with its own
coach_typeandberths, so the search is an exact=match and Rajdhani's B30 no longer appears by mistake. - A fourth or a twentieth coach is just another row. No column is added, and the foreign key checks that every
train_noexists. UNIQUE (train_no, code)lets both trains have an S1 but stops one train from getting two.
Predict the output
One customer, many orders
Use the orders table of the practice database.
Round 1 of 2
How many orders does customer 1 have?
One-to-one and where the key goes
In a one-to-one relationship, each row on one side links to at most one row on the other side. The foreign key goes in the dependent table and is made unique, often as a shared primary key.
A UPI user has at most one KYC (Know Your Customer) record. The diagram shows the link; the script puts the key in the dependent table and makes it unique with a shared primary key.
Example 4 · ER diagram: a UPI user and their KYC record
+----------------+ +----------------+| upi_users | | kyc_details ||----------------| |----------------|| PK user_id | ||------o| | PK,FK user_id || upi_id (UQ) | | pan (UQ) || name | | verified_on |+----------------+ +----------------+One user has zero or one KYC record. Each KYC record belongs to exactly one user. user_id is both the primary key and the foreign key of kyc_details (a shared primary key).
What this code does
o|next to kyc_details: zero or one record, because KYC is optional.||next to upi_users: a KYC record cannot exist without its user.- The key goes in
kyc_details, the dependent side. Marking itPK,FKis what makes the "one" on that side a rule and not a hope.
Example 5 · One-to-one with a shared primary key
CREATE TABLE upi_users ( user_id INT AUTO_INCREMENT PRIMARY KEY, upi_id VARCHAR(50) NOT NULL UNIQUE, name VARCHAR(60) NOT NULL);
CREATE TABLE kyc_details ( user_id INT PRIMARY KEY, -- primary key AND foreign key pan CHAR(10) NOT NULL UNIQUE, verified_on DATE NOT NULL, FOREIGN KEY (user_id) REFERENCES upi_users (user_id));
INSERT INTO upi_users (upi_id, name) VALUES ('anjali@okbank', 'Anjali Verma'), ('farhan@upi', 'Farhan Qureshi'), ('gopal@ybl', 'Gopal Das');INSERT INTO kyc_details VALUES (1, 'ABCPV1234K', '2026-02-11'), (3, 'DXGPD5678M', '2026-03-05');
SELECT u.user_id, u.upi_id, k.pan, k.verified_onFROM upi_users uLEFT JOIN kyc_details k ON k.user_id = u.user_idORDER BY u.user_id;
-- A second KYC record for user 1 is refusedINSERT INTO kyc_details VALUES (1, 'ZZZPV9999Z', '2026-04-01');+---------+---------------+------------+-------------+ | user_id | upi_id | pan | verified_on | +---------+---------------+------------+-------------+ | 1 | anjali@okbank | ABCPV1234K | 2026-02-11 | | 2 | farhan@upi | NULL | NULL | | 3 | gopal@ybl | DXGPD5678M | 2026-03-05 | +---------+---------------+------------+-------------+ ERROR 1062 (23000): Duplicate entry '1' for key 'kyc_details.PRIMARY'
What this code does
- Farhan has no KYC row yet and shows
NULLs through theLEFT JOIN: that is the "zero" in "zero or one". - Because
user_idis the primary key ofkyc_details, a second row for user 1 fails with error 1062 on the keyPRIMARY. The relationship stays one-to-one by rule. - The other way to write it: give
kyc_detailsits ownkyc_idprimary key and declareuser_id INT NOT NULL UNIQUEwith the foreign key. Both are correct.
Why split it into two tables at all? Three good reasons: the extra data is optional (most users have not done KYC, so the columns would be mostly NULL), it is sensitive (PAN details can get stricter access), or it is large and rarely read. If none of these apply, one table is simpler.
Where does the key go? In the dependent table, the one whose rows cannot exist without the other. The key must also be unique: without that, the database quietly allows many rows, and the "one-to-one" is only a hope.
Optional versus required relationships
Optionality is the smallest number of rows on each side of a relationship: required means at least one, optional means zero is allowed. In crow's foot marks, | means at least one and o means zero is fine.
Required or optional? The queries below look at both kinds of link in the practice database: every order must have a customer, but an employee may have no department.
Example 6 · Optional and required links in the practice database
-- departments o|----o{ employees: both sides optionalSELECT d.dept_name, COUNT(e.emp_id) AS staffFROM departments dLEFT JOIN employees e ON e.dept_id = d.dept_idGROUP BY d.dept_id, d.dept_nameORDER BY d.dept_name;
SELECT emp_id, name, dept_idFROM employeesWHERE dept_id IS NULL;
-- customers ||----o{ orders: an order REQUIRES a customerINSERT INTO orders (customer_id, order_date) VALUES (NULL, '2025-06-02');+-------------+-------+ | dept_name | staff | +-------------+-------+ | Engineering | 5 | | Finance | 2 | | HR | 2 | | Research | 0 | | Sales | 3 | +-------------+-------+ +--------+----------------+---------+ | emp_id | name | dept_id | +--------+----------------+---------+ | 13 | Tanvi Kulkarni | NULL | +--------+----------------+---------+ ERROR 1048 (23000): Column 'customer_id' cannot be null
What this code does
- Research has 0 staff (a department with no employees is allowed) and Tanvi Kulkarni has no department yet (
dept_idis nullable). Both "zero" cases exist in real data. orders.customer_idisNOT NULL, so an order with no customer fails at once with error 1048. A required link is aNOT NULLforeign key.
On the child side this is easy to enforce. "Each order has exactly one customer" means customer_id ... NOT NULL, and MySQL rejects an order without one. "Each employee has zero or one department" means a nullable dept_id, so a new joiner can wait for a team.
The parent side is harder. "Every train has at least one coach" (|{) cannot be written as a simple constraint, because the train row has to be inserted before its first coach. Applications enforce such rules in their own code, usually inserting the parent and its first child together.
Next step
Check your understanding
11 quick questions