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
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?