One (mandatory) to One (mandatory)
Diagram 1m21m.png
How to read For any A there must be one B. For any B there must be one A.
Relevant relations none.

Separate entities

Consider the alternatives of embedding the primary key of one table in the other table. In this relation for every A entity there is one and only one B entity (and vice versa). There are actually four alternatives for how we can represent this information. The two most common ways will be to embed the key of A in the B entity or to embed the key of B in the A entity. Under this alternative, the table structure is as follows:

A(__a_id__, a_name, <a_other>)
B(__b_id__, b_name, fk_b_a*, <b_other>)

or

A(__a_id__, a_name, fk_a_b*, <a_other>)
B(__b_id__, b_name, <b_other)

Given this table structure, we must only determine which foreign keys can be null and which are unique. We will concentrate on the first alternative; describing the situation for the second is left as an exercise for the reader. Since the relation between the two entities is one-to-one and mandatory on both sides, the embedded key cannot be null. It is also unique since the key of any one A entity must be embedded one and only one time in the B entity. Given this information, we have the following SQL create statements:

create table A
  (a_id type,
   a_name type,
   <a_other>,
   primary key (a_id));
create table B
  (b_id type,
   b_name type,
   fk_b_a type not null,
   primary key (b_id),
   unique (fk_b_a),
   foreign key (fk_b_a) references A);

Combined entities

The other two alternatives are to combine the two entities into one. This should be done if the embedded entity is not involved in any relations that the other is not. The table structure for this alternative is either of the following:

A(__a_id__, a_name, b_name, <a_other>, <b_other>)

or

B(__b_id__, b_name, a_name, <b_other>, <a_other>)

Since there are no foreign keys, it is a straight-forward step to determine the SQL create statement for the first of the above structures (the other is left as an exercise):

create table A
  (a_id type,
   a_name type,
   b_name type,
   <a_other>,
   <b_other>,
   primary key (a_id));

Back to ER to SQL page.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License