[sql] How to Create a real one-to-one relationship in SQL Server

I have two tables tableA and tableB, I set tableB's primary key as foreign key which references tableA's primary. But when I use Entity Framework database-first, the model is 1 to 0..1.

How does one create a one-to-one relationship in SQL Server?

The easiest way to achieve this is to create only 1 table with both Table A and B fields NOT NULL. This way it is impossible to have one without the other.

A 1 to 1 relationship is very much possible. Even if the relationship diagram doesn't show the 1 to 1 relationship explicitly. If you implement it as below, it will function as a one to one relationship.

I will use a basic example to explain the concept where a single person can only have a single passport. This example works perfectly in MS Access. For the SQL Server version follow this link.

Remember that in MS Access, SQL scripts can only be run one at a time and not as displayed here in sequence.

CREATE TABLE Person
(
Pk_Person_Id INT PRIMARY KEY,
Name VARCHAR(255),
EmailId VARCHAR(255),
);

CREATE TABLE PassportDetails
(
Pk_Passport_Id INT PRIMARY KEY,
Passport_Number VARCHAR(255),
Fk_Person_Id INT NOT NULL UNIQUE, 
FOREIGN KEY(Fk_Person_Id) REFERENCES Person(Pk_Person_Id)
);

What about this ?

create table dbo.[Address]
(
Id int identity not null,
City nvarchar(255) not null,
Street nvarchar(255) not null,
CONSTRAINT PK_Address PRIMARY KEY (Id)
)

create table dbo.[Person]
(
Id int identity not null,
AddressId int not null,
FirstName nvarchar(255) not null,
LastName nvarchar(255) not null,
CONSTRAINT PK_Person PRIMARY KEY (Id),
CONSTRAINT FK_Person_Address FOREIGN KEY (AddressId) REFERENCES dbo.[Address] (Id)
)

1 To 1 Relationships in SQL are made by merging the field of both table in one !

I know you can split a Table in two entity with a 1 to 1 relation. Most of time you use this because you want to use lazy loading on "heavy field of binary data in a table".

Exemple: You have a table containing pictures with a name column (string), maybe some metadata column, a thumbnail column and the picture itself varbinary(max). In your application, you will certainly display first only the name and the thumbnail in a collection control and then load the "full picture data" only if needed.

If it is what your are looking for. It is something called "table splitting" or "horizontal splitting".

https://visualstudiomagazine.com/articles/2014/09/01/splitting-tables.aspx

This can be done by creating a simple primary foreign key relationship and setting the foreign key column to unique in the following manner:

CREATE TABLE [Employee] (
    [ID]    INT PRIMARY KEY
,   [Name]  VARCHAR(50)
);

CREATE TABLE [Salary] (
    [EmployeeID]    INT UNIQUE NOT NULL
,   [SalaryAmount]  INT 
);

ALTER TABLE [Salary]
ADD CONSTRAINT FK_Salary_Employee FOREIGN KEY([EmployeeID]) 
    REFERENCES [Employee]([ID]);

INSERT INTO [Employee] (
    [ID]
,   [Name]
)
VALUES
    (1, 'Ram')
,   (2, 'Rahim')
,   (3, 'Pankaj')
,   (4, 'Mohan');

INSERT INTO [Salary] (
    [EmployeeID]
,   [SalaryAmount]
)
VALUES
    (1, 2000)
,   (2, 3000)
,   (3, 2500)
,   (4, 3000);

Check to see if everything is fine

SELECT * FROM [Employee];
SELECT * FROM [Salary];

Now Generally in Primary Foreign Relationship (One to many), you could enter multiple times EmployeeID, but here an error will be thrown

INSERT INTO [Salary] (
    [EmployeeID]
,   [SalaryAmount]
)
VALUES
    (1, 3000);

The above statement will show error as

Violation of UNIQUE KEY constraint 'UQ__Salary__7AD04FF0C044141D'. Cannot insert duplicate key in object 'dbo.Salary'. The duplicate key value is (1).

There is one way I know how to achieve a strictly* one-to-one relationship without using triggers, computed columns, additional tables, or other 'exotic' tricks (only foreign keys and unique constraints), with one small caveat.

I will borrow the chicken-and-the-egg concept from the accepted answer to help me explain the caveat.

It is a fact that either a chicken or an egg must come first (in current DBs anyway). Luckily this solution does not get political and does not prescribe which has to come first - it leaves it up to the implementer.

The caveat is that the table which allows a record to 'come first' technically can have a record created without the corresponding record in the other table; however, in this solution, only one such record is allowed. When only one record is created (only chicken or egg), no more records can be added to any of the two tables until either the 'lonely' record is deleted or a matching record is created in the other table.

Solution:

Add foreign keys to each table, referencing the other, add unique constraints to each foreign key, and make one foreign key nullable, the other not nullable and also a primary key. For this to work, the unique constrain on the nullable column must only allow one null (this is the case in SQL Server, not sure about other databases).

CREATE TABLE dbo.Egg (
    ID int identity(1,1) not null,
    Chicken int null,
    CONSTRAINT [PK_Egg] PRIMARY KEY CLUSTERED ([ID] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE dbo.Chicken (
    Egg int not null,
    CONSTRAINT [PK_Chicken] PRIMARY KEY CLUSTERED ([Egg] ASC) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE dbo.Egg  WITH NOCHECK ADD  CONSTRAINT [FK_Egg_Chicken] FOREIGN KEY([Chicken]) REFERENCES [dbo].[Chicken] ([Egg])
GO
ALTER TABLE dbo.Chicken  WITH NOCHECK ADD  CONSTRAINT [FK_Chicken_Egg] FOREIGN KEY([Egg]) REFERENCES [dbo].[Egg] ([ID])
GO
ALTER TABLE dbo.Egg WITH NOCHECK ADD CONSTRAINT [UQ_Egg_Chicken] UNIQUE([Chicken])
GO
ALTER TABLE dbo.Chicken WITH NOCHECK ADD CONSTRAINT [UQ_Chicken_Egg] UNIQUE([Egg])
GO

To insert, first an egg must be inserted (with null for Chicken). Now, only a chicken can be inserted and it must reference the 'unclaimed' egg. Finally, the added egg can be updated and it must reference the 'unclaimed' chicken. At no point can two chickens be made to reference the same egg or vice-versa.

To delete, the same logic can be followed: update egg's Chicken to null, delete the newly 'unclaimed' chicken, delete the egg.

This solution also allows swapping easily. Interestingly, swapping might be the strongest argument for using such a solution, because it has a potential practical use. Normally, in most cases, a one-to-one relationship of two tables is better implemented by simply refactoring the two tables into one; however, in a potential scenario, the two tables may represent truly distinct entities, which require a strict one-to-one relationship, but need to frequently swap 'partners' or be re-arranged in general, while still maintaining the one-to-one relationship after re-arrangement. If the more common solution were used, all data columns of one of the entities would have to be updated/overwritten for all pairs being re-arranged, as opposed to this solution, where only one column of foreign keys need to be re-arranged (the nullable foreign key column).

Well, this is the best I could do using standard constraints (don't judge :) Maybe someone will find it useful.

Set the foreign key as a primary key, and then set the relationship on both primary key fields. That's it! You should see a key sign on both ends of the relationship line. This represents a one to one.

Check this : SQL Server Database Design with a One To One Relationship