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

问题描述

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

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.

如何在SQL Server中创建一对一关系?

How does one create a one-to-one relationship in SQL Server?

推荐答案

我很确定SQL Server在技术上不可能具有True 1对1的关系，因为这将意味着您拥有在两个表中同时插入两个记录(否则在插入时会遇到约束错误)，并且两个表之间都具有外键关系.

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.

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

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.

所以有一些伪解决方案

首先，将所有数据存储在一个表中.这样您就不会在EF中遇到任何问题.

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.

第三，也是最有可能的是，您有一个要解决的问题，并且您在问我们为什么您的解决方案不起作用，而不是您要解决的实际问题(XY问题).

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

更新

要在 REALITY 中解释1对1关系如何不起作用，我将使用

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屋！