如何在SQL Server中创建真正的一对一关系 [英] How do I create a real one-to-one relationship in SQL Server

问题描述

我想在SQL Server 2008 R2中创建一对一的关系。

我有两个表 tableA 和 tableB ，我将 tableB 的主键设置为外键，引用 tableA 是主要的。但是当我使用Entity Framework数据库时，模型是1到0..1。

任何人都知道如何在数据库中创建真正的1到1关系？ / p>

提前感谢

解决方案

我很确定在SQL Server中技术上不可能具有True 1到1的关系，因为这意味着您将具有同时插入两个记录（否则您将在插入时遇到一个错误的错误）两个表，两个表之间有一个外键关系。

据说，用外键描述的数据库设计是1到0..1关系。没有约束可能需要在tableB中的记录。您可以与在tableB中创建记录的触发器具有伪关系。

所以有一些伪解决方案

首先，将所有数据存储在一个表中。那么你将在EF中没有任何问题。

或者，您的实体必须足够聪明才能允许插入，除非它具有关联的记录。

或第三，最有可能的是，你有您正在尝试解决的问题，您是否要求我们为什么您的解决方案无法正常工作，而不是您要解决的实际问题（XY问题）。

更新

要解释在 REALITY 中，1到1关系如何不起作用将使用鸡或鸡蛋困境的类比。我不打算解决这个困境，但是如果你有一个限制说，为了在蛋表中添加一个蛋，鸡的关系必须存在，鸡必须存在于表中你不能在蛋桌上添加一个鸡蛋。相反也是如此。没有鸡蛋和鸡蛋中存在的蛋的关系，鸡肉不能添加鸡肉。因此，在数据库中没有任何记录可以在每个数据库中都不会破坏规则/约束之一。

数据库一种关系是误导的。我所看到的所有关系（因为我的经验）将更具描述性，因为一对（零或一）关系。

I want to create a one-to-one relationship in SQL Server 2008 R2.

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.

Any one know how to create real 1 to 1 relationship in database?

Thanks in advance!

解决方案

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

这篇关于如何在SQL Server中创建真正的一对一关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！