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

entity-framework one-to-one sql 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?

10/20/2019 11:15:31 PM

Accepted 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).


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.

12/1/2017 6:08:10 PM

Popular 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.

enter image description here

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

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