One-to-One Relationship

In this relationship, each row/record of the Let’s table is exactly related to one row in the second table and vice versa.

Example: Let’s consider 2 tables “Student” and “Class_Details“. Now “Student” has 2 columns – “Enrolment_No“, “Name” and “Class_Details” has 4 columns “Roll_No“, “Class“, “Division“, and “Enrolment_No“.

In the example, you can see that a student will have only one enrolment number allotted, only one roll number, and only one class and division. Then it may change year-wise or semester-wise but a student cannot have multiple classes or enrollment numbers or roll numbers simultaneously. Here, a one-to-one relationship occurs. Let’s understand One-to-One relationships with the help of a table diagram and their output.

Create tables “Student” and “Class_Details” with required columns or attributes as mentioned in the above example.

Here, you can see that each student’s table record is exactly related to one record in the class_details table. Each student has a unique Enrolment number and Roll number. It is not possible for a student to be enrolled in 2 classes or divisions at same time. Each student is related from its name details and its class uniquely.

SQL Query

Now, to retrieve data from the tables we write the following query to view the details of both the tables.

SELECT students.Enrolment_No, students.Name, students.Roll_No, class_details.Class, class_details.Division
FROM students
JOIN class_details ON students.Enrolment_No = class_details.Enrolment_No;

The above query retrieves records using the primary key and foreign key of another table which is in turn a primary key of the first table. The above query uses ‘Enrolment_No‘ for referencing the records from both tables. In this query, we retrieved data using the primary key of the Student table and as a foreign key of the class_details table.

Output:

Explanation: Through the above output or result we can see that each record of the first table is related exactly to one another record in the next table. You can also notice that in one-to-one relationship output, no multiple records are possible.

In database design, understanding and implementing relationships between entities is crucial. These relationships, such as one-to-one, one-to-many, and many-to-many, establish connections between tables using key constraints.

Let’s explore each type of relationship with examples and SQL implementation.