One-to-many relationship design in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a one-to-many relationship in database design?

It is a relationship where one record in a table (the 'one' side) can be related to many records in another table (the 'many' side). For example, one customer can have many orders.

beginner

How do you represent a one-to-many relationship in SQL tables?

By adding a foreign key column in the 'many' side table that references the primary key of the 'one' side table.

beginner

Why do we use foreign keys in one-to-many relationships?

Foreign keys link records between tables, ensuring data integrity by making sure the 'many' side records relate to a valid 'one' side record.

beginner

Example: If you have tables 'Authors' and 'Books', which table should have the foreign key?

The 'Books' table should have a foreign key column referencing the 'Authors' table because one author can write many books.

intermediate

What happens if you try to insert a record in the 'many' side table with a foreign key value that does not exist in the 'one' side table?

The database will reject the insert to maintain data integrity, because the foreign key must reference an existing record in the 'one' side table.

In a one-to-many relationship, where is the foreign key placed?

A In the table on the 'one' side

B In the table on the 'many' side

C In both tables

D No foreign key is needed

The foreign key is placed in the table on the 'many' side to link back to the 'one' side.

Which SQL constraint ensures that the foreign key value must exist in the referenced table?

A PRIMARY KEY

B UNIQUE

C FOREIGN KEY

D CHECK

The FOREIGN KEY constraint enforces that the value must exist in the referenced table.

If one author can write many books, what kind of relationship is this?

A One-to-many

B One-to-one

C Many-to-many

D No relationship

One author writing many books is a classic example of a one-to-many relationship.

What is the main purpose of using a foreign key in one-to-many relationships?

A To speed up queries

B To store duplicate data

C To create indexes

D To link tables and maintain data integrity

Foreign keys link tables and ensure data integrity by enforcing valid relationships.

Which of these is a valid example of a one-to-many relationship?

A Each customer can place many orders

B Each book has many authors

C Each employee works in many departments

D Each student has one ID card

One customer placing many orders is a one-to-many relationship.

Explain how to design a one-to-many relationship between two tables in a database.

Think about which table holds the foreign key and why.

Describe a real-life example of a one-to-many relationship and how it would be represented in SQL tables.

Consider something like customers and orders or authors and books.