亲子一对一关系相同表 [英] Parent-Child one-to-one Relation same Table

问题描述

我有一个关于如何在Microsoft SQL Server 2012中实现表关系的问题。

我有一个表（MyTable），它应该有一个父/子结构体。
一个父母可能有一个孩子，一个孩子只有一个父母。这是一种古典的一对一关系。最上面的条目显然可能没有父级。 （这是通过可空属性建模的）

当我尝试在MS-SQL中建模时，我收到如下表。

这是我的代码：

  CREATE TABLE [dbo]。[MyTable]（
 [Id] [uniqueidentifier] PRIMARY KEY NOT NULL，
 [Name] [nvarchar]（200）NOT NULL，
 [ParentObjectId] [uniqueidentifier] NULL 
）
 
 GO 
 
 ALTER TABLE [dbo]。[MyTable] WITH CHECK ADD CONSTRAINT [FK_MyTable_ParentObject] FOREIGN KEY（[ParentObjectId]）
参考[dbo]。[MyTable]（[Id]）
 GO 
 
 ALTER TABLE [dbo]。[MyTable] WITH CHECK ADD CONSTRAINT [Unique_ParentId] UNIQUE（ParentObjectId）
 GO 
 
 ALTER TABLE [dbo]。[MyTable] CHECK CONSTRAINT [FK_MyTable_ParentObject] 
 GO 
  

UNIQE约束应该确保最多只有一个父母一个孩子。

但实体框架不会让我将属性更改为一对一的关系。它总是生成一对多的关系。

编辑：
代码是DB-First。

你有什么想法可以在MS-SQL和EntityFramework中正确建模吗？

解决方案>

不幸的是，你不能在EF中做你想要的。

只有当共享主键（即两个表）都支持一对一的映射必须有相同的主键，一个必须是另一个的外键）。使用自引用表时，您不能这样做。

EF不支持这一点的原因是EF不支持唯一约束，因此，它无法确保1：1映射受到约束。这可能会改变，因为EF现在支持唯一索引，但是这并没有改变一对一共享主键的要求。

最好的方法是创建一对多

I have a question about how to realize a Table relationship in Microsoft SQL-Server 2012.

I have a Table (MyTable) which should hold a Parent/Child structure. One Parent may have one Child and one Child only has one Parent. This is a classical one-to-one relation. The topmost entry obviously may not have a parent. (This is modelled via a nullable property)

When I try to model this in MS-SQL I receive a table as follows.

Here is my Code:

CREATE TABLE [dbo].[MyTable](
    [Id] [uniqueidentifier] PRIMARY KEY NOT NULL,
    [Name] [nvarchar](200) NOT NULL,
    [ParentObjectId] [uniqueidentifier] NULL
)

GO

ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [FK_MyTable_ParentObject] FOREIGN KEY([ParentObjectId])
REFERENCES [dbo].[MyTable] ([Id])
GO

ALTER TABLE [dbo].[MyTable]  WITH CHECK ADD  CONSTRAINT [Unique_ParentId] UNIQUE(ParentObjectId)
GO

ALTER TABLE [dbo].[MyTable] CHECK CONSTRAINT [FK_MyTable_ParentObject]
GO

The UNIQE Constraint should assure that there is at most one parent for one child.

BUT Entity Framework won't let me change the properties to be a one-to-one relation. It always generates a one-to-many relation.

EDIT: The code is DB-First.

Do you have any idea on how to model this properly in MS-SQL and EntityFramework?

解决方案

You can't do what you want in EF, unfortunately.

EF only supports one to one mappings when there is a shared primary key (ie both tables have to have the same primary key, and one has to be a foreign key of the other). You can't do this when using a self-referencing table for obvious reasons.

The reason EF doesn't support this is that EF doesn't support unique constraints, therefore it has no way to ensure that the 1:1 mapping is constrained. This could change because EF does now support unique indexes, however this hasn't changed the requirement for shared primary keys in one to one's.

The best you can do is create a one to many.

这篇关于亲子一对一关系相同表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！