# One and only one relationship - ER diagram to relational schema

I'm taking this term a DB course as part of my CS studies.
This week we began studying the subject of ER diagrams, which I find a bit confusing.

I have some uncertainty about the picture above (Note: the rounded arrows indicate "One and only one" - that is, every subject from B can and must be directed to exactly one subject in A, and vice versa).

I have to create a suitable relational schema for this diagram, and here is my try:

• A(a, b, c, d) (b is the key)
• B(e, f) (e is the key)

This is my uncertainty:

R(b, e) - no matter which key I'm creating out of those foreign keys, the "one and only one" constraint isn't held.

How can I create the schema where this constraint holds?

What about if you had "at most one" arrows, and not exactly?

Table a should reference (foreign key) b and vice versa. On the foreign keys you create a unique index, so you have a 1 on 1 relationship.

The following data respects these constraints, but is probably not what you'd like.

``````Tab a   Tab b
PK FK   PK FK
1   a   a   2
2   b   b   1
``````

So you may need to be even more stringent, and put a foreign key on the primary key column, forcing both tables to have the same primary key.

"at most one": allow the column to be null.

A symmetric one on one relationship, as below, can't be enforced with foreign keys (unless logic is added, eg. triggers).

``````Tab a   Tab b
PK FK   PK FK
1   b   a   2
2   a   b   1
``````
• What are those a and b in your tables' columns? What do those numbers represent? I can't see the meaning of your tables. Oct 25, 2020 at 7:39
• They are column values. The value of the primary key of the first rown of table a is 1. The value of the foreign key of the second row of table b is also 1. a(1) is related to b(a), b(a) is related to a(2), a(2) is related to b(b), and b(b) is related to a(1). I completely understand that you don't, because although there is a one to one relationship, it is not symmetric, which is probably what you are after. I think that can only be implemented by giving both tables the same PK, referencing each other. Oct 25, 2020 at 10:16
• BTW, if you have notions of arithmetic, the relation you're after is called a "bijection". en.wikipedia.org/wiki/Bijection Oct 25, 2020 at 13:18
• There is a term "shared primary key" that is used to indicate one of your options above. Oct 25, 2020 at 16:09

### Solution 1

Define a single table:

``````AB(a b c d e f)
``````

with two candidate keys, `b` and `e`. You chose one of them as primary key, and declare the other as unique.

Advantages: All the constraints of the association among the two entities are satisfied.

Disadvantages: If there are other entities related to `A` or `B`, then the foreign key for the relation corresponding to this entity can be not “natural” if the primary key chosen for `AB` is the “other” key.

### Solution 2

Define one of the two relations with a not nullable foreign key for the other, for instance add `fke` (“foreign key for attribute `e` in `B`”) to relation `A`. Then declare that this attribute is unique.

Advantages: it is more “natural” to keep the two entities in separate relations. You keep the constraints that each element of `A` is related to one and only one element of `B` (since the attribute `fke` is not nullable and it is unique in all the relation).

Disadvantages: you lose one constraints: that each element of `B` is related to some element of `A`. This constraint must be enforced in the applications developed on the database.

### Solution 3

As before, exchanging the role of `A` and `B`.

### Solution 4

As you have mentioned, with a bridge table `R(b,e)`. Then declare both `b` and `e` as unique.

Advantages: you keep the constraint that each element of `A` can be related only to one element of `B`, and vice versa.

Disadvantages: you lose the constraints that each element of `A` is related to some element of `B` and vice versa. Those constraints must be enforced by the applications. Moreover, you have one more table, and this make the queries more complex.

### Solution 5

You put a foreign key in each of the two tables to refer the other table. These foreign keys are also declared unique.

Advantages: you keep the constraint that each element in `A` is related to one and only one element in `B`, and vice versa.

Disadvantages: you lose the constraint that if a certain element `b1` of `B` is associated to an element `a1` of A, then `a1` is associated to `b1`. In fact this solution allows inconsistences: an element `a1` could be associated to `b2`, `a2` could be associated to `b1`, while at the same time the foreign key for `b2` could refer to `a2`, and the foreign key for `b1` could refer to `a1`. So this constraint should be enforced by the application. Moreover, there is a certain redundancy in the data.

The solutions are listed in order from the more “secure”, to the more “dangerous”, at least in my opinion.

Finally, is worth to note an important point from this case: when translating from the ER model to the Relational Data Model, there are situations in which the translation is not completely satisfactory.

For your last question: what happen if at most one arrow. In this case the second or third solution can be used. And normally, if one arrow is total (i.e. you have exactly one element in the other entity set) while the other is partial, you put the foreign key in the relation for which the relation is total. If both are partial, then you can choose freely the relation in which to put the foreign key.

• How do I draw this relational schema concretely? For example, for the first solution? Oct 25, 2020 at 7:02
• Solution 5 does not allow inconsistencies if each PK references the PK in the other table. Oct 25, 2020 at 19:30
• @ypercubeᵀᴹ , in solution 5 it is not sufficient to declare reciprocal foreign keys. It is necessary that if tuple a1 refers to tuple b1, then tuple b1 must refer exactly to tuple a1, and not to some other tuple in A. The inconsistency can happen if data is entered in a wrong way. Oct 25, 2020 at 21:17
• @HelpMe , you ask about drawing the relational schema. Are you thinking about some kind of graphical notation? Oct 25, 2020 at 21:19