Implementing One-to-One Relationships

In order to implement a one-to-many relationship we use a foreign key constraint.

To implement a many-to-many relationship we use a “link table” (a.k.a. “junction table”, “association table”, “intersection table”, and other names) with two foreign key constraints – from the link table to each one of the original entities’ tables. We actually convert the logical many-to-many relationship into two physical implementations of one-to-many relationships.

But what about one-to-one relationships?

Suppose we have the entities EMPLOYEE and CAR, with the business rules “each employee may have one and only one car” and “each car may belong to one and only one employee”.

We can use a combination of a foreign key constraint with a unique constraint to force this relationship.

First we choose which of the two entities will reference the other one. This decision may be influenced by the ordinality of the relationship (if one side is mandatory and the other one is optional, we tend to add the referencing column to the mandatory side), and by the usage of the tables by the application.

In our example we choose that the EMPLOYEES table will reference the CARS table:

create table cars (
       car_id        number       not null,
       license_plate varchar2(20) not null,
       color         varchar2(20)         ,
       constraint cars_pk primary key (car_id)
);

create table employees (
       emp_id     number       not null,
       first_name varchar2(20) not null,
       last_name  varchar2(30) not null,
       car_id     number               ,
       constraint emp_pk primary key (emp_id)
);

We add a foreign key constraint to make sure employees refer only to existing cars:

alter table employees add 
   constraint emp_fk_cars foreign key (car_id)
   references cars (car_id);

Still, several employees may refer to the same car. So we add a unique constraint:

alter table employees add 
   constraint emp_car_uk unique (car_id);

And now each car may be referenced by at most one employee.

Sometimes both tables have the same primary key (for example, when one of the tables is actually an extension of the other table, so both tables represent different aspects of the same entity). In this case, we only need to add a foreign key constraint on the primary key column(s) of one of the tables. There is no need to add another referencing column (as the primary key column does it naturally), and there is no need to add a unique constraint (as the primary key constraint already enforces the uniqueness).