|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, <a_other>, primary key (a_id)); create table B (b_id type, b_name type, fk_b_a type not null, <b_other>, primary key (b_id), unique (fk_b_a), foreign key (fk_b_a) references A);
Back to ER to SQL page.