4

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?
  • 3
    You'd use a Deferred Constraint, which aren't checked until the transaction is committed. – Philᵀᴹ Jul 1 '18 at 8:50
  • @Philᵀᴹ I’m not aware of Deferred Constraint. How do you implement that? – Manngo Jul 1 '18 at 8:53
  • What do you mean exactly by "too useful"? I am very surprised to see Microsoft removing a feature that both Oracle and PostgreSQL! I've never seen a construct like CREATE TABLE more ( id INT PRIMARY KEY REFERENCES stuff(id), data VARCHAR(255) ); deferred constraints are normally for FOREIGN KEYs, not PRIMARY ones? – Vérace Jul 1 '18 at 11:17
  • I don't think that SQL Server ever supported deferred constraints – ypercubeᵀᴹ Jul 1 '18 at 14:19
  • 1
    @ypercubeᵀᴹ I was thinking of stackoverflow.com/questions/5974731/… where it is mentioned, and docs.microsoft.com/en-us/sql/relational-databases/… where the flag DISABLE_DEF_CNST_CHK is deprecated. – Manngo Jul 1 '18 at 22:32
2

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.

  • Two examples why a genuine one-to-one relationship might be: (a) you can implement a form of security by putting more privileged columns into a separate table and (b) it might simplify managing data which might be organised in sections. I some cases, you can also do this to link to a table in another database. – Manngo Jul 1 '18 at 7:53
  • @Manngo for both cases you mention, you don't need both tables to refer to each other (or N others in that case), their rows would exist if they have any data to store and, if applicable, you would have a master table with N extension tables, with the extensions holding the foreign keys. – EzLo Jul 2 '18 at 7:20
  • Not necessarily. For example, I might have user passwords in a separate table, or sensitive employee details which are still required. However, I agree that this still puts one table in a controlling position. I would still be curious as to whether this is meant by the one-to-one relationship. – Manngo Jul 2 '18 at 7:36
  • 2
    "And you wouldn't be able to insert any rows without disabling the foreign keys first". Please correct this. Yes, you can do that, in all databases that have implemented deferred constraints. Also in databases that allow inserts into multiple tables in one statement. – ypercubeᵀᴹ Jul 2 '18 at 10:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.