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

问题描述

我有两个表 Country 和 Capital，我将 Capital 的主键设置为引用 Country的主要.但是当我使用实体框架数据库优先时，模型是 1 到 0..1.

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.

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

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

推荐答案

我很确定在 SQL Server 中技术上不可能有真正的 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 关系如何行不通，我将使用 先有鸡还是先有蛋的困境.我不打算解决这个困境，但是如果你有一个约束，说为了向 Egg 表中添加一个 Egg，Chick 的关系必须存在，并且表中必须存在该鸡，那么你不能在 Egg 表中添加一个 Egg.反之亦然.如果不存在与 Egg 和 Egg 表中的 Egg 的关系，则无法将 Chicken 添加到 Chicken 表中.因此，在不违反任何规则/约束的情况下，不可能在数据库中创建所有记录.

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