One-to-One Relationship exists when a single record in the 1st table is having a relationship with only one record in the 2nd table, and similarly, we can say that a single record in the 2nd table is related to only one record in the 1st table.
One-to-One Relationship Examples
Let’s begin with an ERD design in which one student can have only one scholarship.
Entities
Student
Scholarship
Attributes of Student
Student_id
class
Attributes of Scholarship
scholarship_id
scholarship_type
scholarship_year.
Relationship
One Student can have one scholarship.
Figure: Entity Relationship Diagram of 1 to 1 Relationship
How to convert ERD into database tables and showing a 1 to 1 relationship?
Now, let’s convert this ERD Design to database tables.
As, we have two entities and one relationship, so we need to make three tables, two tables for two entities and one table for this relationship.
The first table is for “Student” entity
The second table is for a relationship that “student_can_have_scholarship”. We can also handle the situation without creating an extra relationship table in one to one relationships (but not in other types of relationships).
The third table is for “scholarship” entity
Figure: Database tables representation of 1 to 1 RelationshipNon-Key Attributes: are the attributes without any key.
Primary-Key Attributes: are the attributes with the primary key and used for the unique identification of any row. Any value in the primary key attribute can’t be repeated again and again.
Foreign- Key Attributes are the attributes that set a foreign key. A foreign key is the primary key of one table that is linked with the field of another table.
Detailed information about keys is available in the keys tutorial.
In this example, we have set the primary key with “student_id” in the 2nd table, but we can also set the primary key with “scholarship_id”. Because the values of both attributes are unique and values are not repeating.
Note: We can also handle the situation without creating an extra 3rd table in one to one relationships (but not in another type of relationship).
One-to-one relationship example in SQL | when to use a one-to-one relationship | one-to-one relationship diagram | one to one relationship in MySQL | one to one relationship in the SQL server | one to one relationship in XAMPP PHP Myadmin | one-to-one relationship ER diagram.
Next Articles
