One-to-Many Relationships in SQL (with Examples)

Database Management Systems have data organized in tables that relate to another set of tables which sums up the relational databases. Tables have attributes that have relationships with other attributes in another table. Any established database system will have one-to-one, one-to-many, and many-to-many relationships. 

Relationships in SQL

These different types of relationships in SQL such as one-to-one, one-to-many, and many-to-many define user-entity relationships. Any Entity Relationship Diagram will represent a graphical view of the inter-database connections. 

These relations describe the existing purpose or reference of the database. To define these relationships visually we use the Entity Relationship (ER) Diagram.

For example, One-to-many relationships can be defined between a user and his login details on an e-commerce website. The user is related to his user ID and the password is a one-to-one relationship. 
 
Another example defining one-to-many relationships is when a teacher is related to several students in a class, this multi-association of a single entity is known as many relationships.

Whereas many-to-many-to-many relationships are when multiple entities are connected to multiple ends. For example, when several authors are related to several books, this association is known as a many-to-many relationship.

One-to-Many Relationship in SQL

As discussed earlier one of the most commonly used relationships in database management systems is one-to-many relationships. This occurs when one entity, suppose student_id is related to more than one entity such as several subjects. These tables can be joined using different types of joins considering the primary and foreign keys.

SQL Query:

CREATE TABLE Authors (
AuthorID INT PRIMARY KEY,
AuthorName VARCHAR(255) NOT NULL
);

INSERT INTO Authors (AuthorID, AuthorName) VALUES
(1, 'Jane Austen'),
(2, 'George Orwell'),
(3, 'J.K. Rowling');

SELECT * FROM Authors;

Output:

SQL Query:

CREATE TABLE Books (
BookID INT PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
PublicationYear INT,
AuthorID INT,
FOREIGN KEY (AuthorID) REFERENCES Authors(AuthorID)
);

INSERT INTO Books (BookID, Title, PublicationYear, AuthorID) VALUES
(101, 'Pride and Prejudice', 1813, 1),
(102, '1984', 1949, 2),
(103, 'Harry Potter and the Philosopher''s Stone', 1997, 3),
(104, 'Sense and Sensibility', 1811, 1),
(105, 'Animal Farm', 1945, 2);

SELECT * FROM Books;

Output:

In this example, we can see that authors with AuthorID ‘1’ and ‘2’ have multiple books written or associated with them in the database. This association is known as one-to-many relationships.

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

A One-to-many relationship is defined as having multiple rows in one table associated with a single row in another table. This relationship is sustained through a foreign key in the table which refers to multiple entities and the primary key in the table represents the single entity on the other side. These keys establish association based on given references in the database.

How do you make a one-to-many relationship?

To make a one-to-many relationship in SQL, we will have to use the foreign key. The table with multiple associations to another table has a foreign key which refers to the primary key in the table. This relationship maintains data integrity and consistency to allow further modification and retrieval of data.

While making a one-to-many structure developers can use Entity Relationship Diagram (ER-Diagram) to establish the relationship between the entities in a graphical form.

Conclusion

Overall in this article, we learned about various types of relationships in database management systems, along with one-to-many relationships. These types of relationships are defined to access different entities from a table that are associated with each other. Further, these types of relationships help in a smoother process of data retrieval functions.