Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I have two tables Country and Capital, I set Capital's primary key as foreign key which references Country'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?

enter image description here

like image 871
James Avatar asked Apr 24 '12 05:04

James


People also ask

How do you create a one-to-many relationship table?

To establish a one-to-many relationship, the primary key of table A (the "one" table) must be the secondary key of table B (the "many" table).

What is an example of a one-to-one relationship?

Here are some examples of one-to-one relationships in the home: One family lives in one house, and the house contains one family. One person has one passport, and the passport can only be used by one person. One person has one ID number, and the ID number is unique to one person.


1 Answers

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

like image 83
Erik Philips Avatar answered Sep 30 '22 04:09

Erik Philips