One (optional) to One (mandatory)
Diagram 1o21m.png
How to read For any A there is one and only one B. For any B there may be one A.
Relevant relations Of course, if A were mandatory and B optional, the table names in this discussion would just be reversed.

Consider the alternatives of embedding the primary key of one entity in the other entity. Suppose we put the b_ids in A. By embedding the b_id in the A table, we are associating one and only one B entity with a particular A entity. By the ER diagram, for some A entities there may not be an associated B entity. Thus, there may be some nulls in the foreign key. By rule #2 we would prefer to have no nulls in a column if possible.

Suppose we put the a_ids in B. By embedding the a_id in the B table, we are associating one and only one A entity with a particular B entity. By the ER diagram, for every B entity there is one and only one associated A entity. Thus, there will be no nulls in the foreign key. This is the preferred state of affairs. Given the above discussion, the table structure is as follows:

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

Given this table structure, we must only determine which foreign keys can be null and which are unique. The column fk_b_a is the only foreign key in these two tables. We know from the above discussion that it cannot be null. The value must also be unique since any particular A entity can be associated with no more than one B entity. Given this information, we have the following SQL create statements:

create table A
  (a_id type,
   a_name type,
   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);

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