Parent-Child one-to-one Relation same Table

Question

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?

Answer 1

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.