Understanding One to One Relationships

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?

1 to 1 (or possibly 0) relationships are not cross referenced, they are implemented as your 2nd example, with one of both entities related to the other one (and not the other way around).

Linking both ways would be redundant for one of the relationships, as you just need 1 to know the nature of the link. Also, in databases that don't support deferrables foreign keys or multiple inserts in one statement (thanks to @ypercubeᵀᴹ for this note), you won't be able to insert the rows without disabling the foreign key first.

If the existance of stuff depends on more and more depends on stuff then there is a problem of the chicken and the egg. Which one really represents the entity you are trying to store? Do you really need 2 different tables for them? Does your entity exist only when both records are created?

1 to 1 relationships always raise an eyebrow when designing. Maybe if you had a particular case scenario to share we could delve more in detail. The only few exceptions I've implemented 1 to 1 relationships are when we inherited previous databases with tables that had 100+ columns, and we split them up for performance and storage issues, but not for pure designing reasons.

StackExchange Database Administrators Q#211054, answer score: 3