How to implement a one-to-one relationship in a…

The "one-to-one" relationship in a relational database is implemented using a unique constraint and a foreign key. The most common methods:

Using a shared primary key: Two tables use the same field as the primary key, which is also a foreign key referencing the primary key of the other table.
Using a unique foreign key: One of the tables creates a field that is a foreign key referencing the primary key of the other table, with a uniqueness constraint applied to this field.

Example with a shared primary key (in SQL Server):

CREATE TABLE UserProfiles (
    UserId INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50)
);

CREATE TABLE UserDetails (
    UserId INT PRIMARY KEY, -- This is both the primary key and foreign key
    Address NVARCHAR(100),
    Phone NVARCHAR(20),
    CONSTRAINT FK_UserDetails_UserProfiles FOREIGN KEY (UserId) REFERENCES UserProfiles(UserId)
);

Example with a unique foreign key (in SQL Server):

CREATE TABLE Users (
    UserId INT PRIMARY KEY,
    Username NVARCHAR(50) UNIQUE
);

CREATE TABLE UserInfo (
    UserInfoId INT PRIMARY KEY, -- Primary key for UserInfo
    UserId INT UNIQUE, -- Unique foreign key referencing Users
    Email NVARCHAR(100),
    CONSTRAINT FK_UserInfo_Users FOREIGN KEY (UserId) REFERENCES Users(UserId)
);

The choice between these approaches depends on the semantics of the data and access convenience. Using a shared primary key is often employed when the data in the second table is an optional extension of the first. A unique foreign key is more flexible if the primary keys of the tables do not initially match.

How to implement a one-to-one relationship in a database?