|Lesson 4||One-to-one relationships, part 1|
|Objective||Define one-to-one Relationships|
A one-to-one (1:1) relationship exists when zero or one instance of
entity A can be associated with zero or one instance of
entity B, and zero or one instance of
entity B can be associated with zero or one instance of
To illustrate such a relationship graphically, think of entities A and B in terms of generic tables, where generic tables are not populated with data.A one-to-one relationship exists when one record in Table A can be associated with one record in Table B, and one record in Table B can be associated with one record in Table A. The following Slide Show illustrates a 1:1 relationship between Table A (entity A) and Table B (entity B) from the viewpoint first of Table A and then of Table B.
- 1:1 relationship between Table A (entity A) and Table B (entity B) from the viewpoint of Table A.
- The same relationship between Tables A and B appears this way from the viewpoint of Table B.
When transforming a one-to-one relationship, you create a foreign key and a unique key and all columns of this foreign key are also part of a unique key.
If the relationship is mandatory on one side, the foreign key is created at the corresponding table.
If the relationship is mandatory on both sides or optional on both sides, you can choose on which table you want to create the foreign key. There is no absolute rule for deciding on which side to implement it.
If the relationship is optional on both sides you may decide to implement the foreign key in the table with fewer numbers of rows, as this would save space. If the relationship is mandatory at both ends, we are facing the same RDBMS limitation you saw earlier.
Therefore, you need to write code to check the mandatory one at the other side, just as you did to implement m:1 relationships that are mandatory at the one end.
The next lesson explores one-to-one relationships further.
Relational Database Design