In SQL Server, how do you make a true one-to-one relationship?

entity-framework one-to-one sql sql-server


I've got two tables.tableA and tableB , I settableB as a foreign key that references the main keytableA is important. However, the model is 1 to 0..1 when I use Entity Framework database-first.

In SQL Server, how do you establish a one-to-one relationship?

10/20/2019 11:15:31 PM

Accepted Answer

I believe it is theoretically impossible to have a True 1 to 1 connection in SQL Server since you would have to put both entries simultaneously into each table, with each table having a foreign key relationship to the other, in order to avoid a constraint error on insert.

In light of this, the connection in your database architecture that a foreign key describes is a 1 to 0..1 relationship. There is no constraint that may possibly call for a record in tableB. A trigger that produces the record in tableB allows for the creation of a pseudo-relationship.

There are a few false answers as a result.

Put all the data in a single table first. Then EF won't cause you any problems.

Or, alternatively, your organization has to be astute enough to prevent inserts unless they are accompanied by a record.

Alternatively, the most probable option is You are attempting to fix a problem, but instead of addressing the issue at hand, you are asking us why your proposed solution doesn't work (an XY Problem).


I'll use the example of the The egg or the chicken conundrum to illustrate why one-to-one connections don't function in the REALITY. I don't want to provide a solution to this conundrum, but if there were a restriction that said that in order to add an Egg to the Egg table, the connection with the Chicken must exist and the Chicken must be present in the table, you couldn't do so. The inverse is also accurate. Without both the link to the Egg and the Egg being present in the Egg table, you cannot add a Chicken to the Chicken table. As a result, it is impossible to create a record in a database without also violating one of the rules or constraints.

The database's representation of a one-to-one connection is deceptive. The best way to describe any connection I've ever encountered is as a one-to-(zero or one) relationship.

12/1/2017 6:08:10 PM

Popular Answer

Establish the connection on both the main key fields and the foreign key as primary keys. I'm done now! On both sides of the relationship line, you should spot a crucial indicator. This is an example of a 1:1.

enter image description here

Verify this: zzzz-12 zzzz

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow