Understanding One to One Relationships

by Manngo   Last Updated July 01, 2018 00:06 AM

As far as I understand, a one-to-one relationship between two tables means that each row in one table matches exactly one row in the other.

There a a number of reasons why this is useful, such as virtually adding columns to a table without actually altering it.

The above description would suggest that the relationship is symmetrical: a row in either table is matched by a row in the other.

This is readily done if the primary key is also a foreign key to the other:

CREATE TABLE stuff (
    id INT PRIMARY KEY REFERENCES more(id),
    data VARCHAR(255)
);

CREATE TABLE more (
    id INT PRIMARY KEY REFERENCES stuff(id),
    data VARCHAR(255)
);

I can see a logistic problem here: how can you add a row to one table when there isn’t a row in the other table to match?

A more relaxed version is a one-to-zero-or-one relationship, which I prefer to call a one-to-maybe relationship. This is easily implemented if one table references the other, but not the other way round:

CREATE TABLE stuff (
    id INT PRIMARY KEY, --  does not reference the more table
    data VARCHAR(255)
);

The relationship is still between primary keys, but only in one direction. This is logistically easier, and can be used to implement optional columns without getting into a fight about the use of NULLs.

The question is:

  • Does a one-to-one relationship really refer to the first case, where two tables match in both directions?
  • How would you go about adding a row in that case?


Related Questions




Counting in Tuple Relational Calculus

Updated May 02, 2018 10:06 AM


Confusion with Tuple Relation Calculus

Updated September 18, 2018 09:06 AM