How to Create a real one-to-one relationship in SQL Server

Question

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

User · Answer

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.

User · Answer

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

User · Answer

I m pretty sure it is technically impossible in SQL Server to have a True 1 to 1 relationship  as that would mean you would have to insert both records at the same time  otherwise you d get a constraint error on insert   in both tables  with both tables having a foreign key relationship to each other   That being said  your database design described with a foreign key is a 1 to 0  1 relationship   There is no constrain possible that would require a record in tableB   You can have a pseudo-relationship with a trigger that creates the record in tableB    So there are a few pseudo-solutions  First  store all the data in a single table   Then you ll have no issues in EF   Or Secondly  your entity must be smart enough to not allow an insert unless it has an associated record   Or thirdly  and most likely  you have a problem you are trying to solve  and you are asking us why your solution doesn t work instead of the actual problem you are trying to solve  an XY Problem    UPDATE  To explain in REALITY how 1 to 1 relationships don t work  I ll use the analogy of the Chicken or the egg dilemma   I don t intend to solve this dilemma  but if you were to have a constraint that says in order to add a an Egg to the Egg table  the relationship of the Chicken must exist  and the chicken must exist in the table  then you couldn t add an Egg to the Egg table   The opposite is also true   You cannot add a Chicken to the Chicken table without both the relationship to the Egg and the Egg existing in the Egg table   Thus no records can be every made  in a database without breaking one of the rules constraints   Database nomenclature of a one-to-one relationship is misleading   All relationships I ve seen  there-fore my experience  would be more descriptive as one-to- zero or one  relationships

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server

Examples related to entity-framework

Examples related to one-to-one