Create one to one relationship by using entity framework database first

Question

In EF Code First, we can create one-to-one relationship by coding like this:

public class User
{
public int UserID {get;set;}
public string Name {get;set;}
public int UserDetailID {get;set;}

public UserDetail Detail {get;set;}
}

public class UserDetail
{
public int UserDetailID {get;set;}
public string Address {get;set:}
public int UserID {get;set;}

public User User {get;set;}

}

However, when I tried to create the same relationship by using EF Database first in visual studio 2012, I got in trouble. Here is my code:

CREATE TABLE [dbo].[Users] (
[UserID]                 UNIQUEIDENTIFIER CONSTRAINT [DF_Users_UserID] DEFAULT (newid()) NOT NULL,
[UserDetailID]                 UNIQUEIDENTIFIER NOT NULL,
[Name]                      NVARCHAR (50)    NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([UserID] ASC),
CONSTRAINT [FK_Users_UserDetails] FOREIGN KEY ([UserDetailID]) REFERENCES [UserDetails]([UserDetailID])
);

CREATE TABLE [dbo].UserDetails] (
[UserDetailID]     UNIQUEIDENTIFIER CONSTRAINT [DF_UserDetails_UserDetailID] DEFAULT (newid()) NOT NULL,
[UserID]           UNIQUEIDENTIFIER NOT NULL,
[Address]             NVARCHAR(100) NOT NULL,
CONSTRAINT [PK_UserDetails] PRIMARY KEY CLUSTERED ([UserDetailID] ASC),
CONSTRAINT [FK_UserDetails_Users] FOREIGN KEY ([UserID]) REFERENCES [dbo].[Users] ([UserID])

The error message is something like

"Error  2   SQL01767: Foreign key 'FK_Users_UserDetails' references invalid table 'UserDetails'.    

I think the reason for this error probably be when it tries to reference the foreign key "UserDetailID", it finds that it hasn't been created yet. But I don't know how to fix this, and I don't even know this is the way to do it, I know doing one-to-one relationship with EF is tricky, or some people even says it's impossible. Can anyone give me any suggestion? Thank you.

Update: Just to clarify my case, I am trying to design the database in visual studio 2012 database project, then publish it to the SQL server, afterward, create/update my .edmx file from the database in SQL server. I am not sure about how to create a one-to-one relationship that the EF can recognize correctly and create the right classes in .edmx file.

Solution 1

Creating a 1:1 relationship is not that tricky and certainly not impossible, although it is not a particularly common requirement and in this case I can't see why you would want it? If people are saying this then you are talking to the wrong people.

Anyhow using SQL queries as you seem to be is not to do with EF, you are just working directly with the database, In the first CREATE you are trying to add the constraint but you haven't created the other table yet... As you mentioned in your question.

I think you need to create both tables first and then add the constraint with ALTER TABLE.

Additionally searching SO for questions about 1:1 turns up quite a lot so I suggest you do that.

EDIT: So using a database project (I only have VS Express so I don't have those) you want to create a "1:1" relationship using SQL and then add an Entity Data Model to a (probably different) project which references the database and automatically create 1:1 relationship?

That is a whole different story unfortunately. When I was talking about possibility to create 1:1 that was in reference to EF only and not to databases as such. It is actually very difficult/impossible as you said to create 1:1 in SQL. I think that it makes sense that in order to insert into a 1:1 realationship you would need to somehow insert into both tables at exactly the same time or fiddle about with disabling constraints briefly when adding rows.

In general there are a few different option.

1. Don't split the tables unnecessarily. In true 1:1 all data is required so the only reason to split is for performance reasons (e.g partioning) which I would avoid in this case.

2. Map multiple table to a single entity as show here.

3. Create a 1:0..1 relationship and enforce you own requirements in the application.

In either option 2 or 3 you can use the following SQL to create a relationship which uses the same PK on the second table as the FK in the relationship.

CREATE TABLE [dbo].[Users] (
[UserID] UNIQUEIDENTIFIER CONSTRAINT [DF_Users_UserID] DEFAULT (newid()) NOT NULL,
[Name] NVARCHAR (50)    NOT NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED ([UserID] ASC),
);

CREATE TABLE [dbo].[UserDetails] (
[UserID]           UNIQUEIDENTIFIER NOT NULL,
[Address]             NVARCHAR(100) NOT NULL,
CONSTRAINT [PK_UserDetails] PRIMARY KEY CLUSTERED ([UserID] ASC),
CONSTRAINT [FK_UserDetails_Users] FOREIGN KEY ([UserID]) REFERENCES [dbo].[Users] ([UserID]) ON DELETE CASCADE 
);

I suggest you also use store generated identity as well where you can.

Solution 2

Just remove UserDetailID from the UserDetail table and make UserID both primary key and a foreign key to the UserID column of the User table.

This the correct way to make 1:1 relationships in a database and EF recognizes it and maps the entities appropriately with database-first approach.

Solution 3

The question is a couple years old.. and the ef version wasn't stated.. but one answer is to remove UserDetailID from both tables. UserID should be the only primary key on both tables.

the 'unqieidentifier' (GUID) data type shouldn't pose an issue (opposed to using INT), but you certainly don't want to populate it with newId..

Comments

[Ibrahim Najjar]

you mean using EF Model First, because Database First is used when you already have a database ?

[Dave Williams]

you need to make both tables before you attempt to insert the foreign key.

[yan]

@Sniffer Please see my update. Thanks.

[Ibrahim Najjar]

you shouldn't be using Database First, you should be using Model First, it was specifically made for these situations.

[yan]

Thank you Dave. A follow-up question will be, how do I create the table without constraints first, then add constraints in visual studio 2012 database project?

[Dave Williams]

It is easy enough to create tables and then add constraints using ALTER TABLE which you can find info about on SO, MSDN or wherever. However I don't think it will help you... see updated answer.

[yan]

Thank you, you've got my point, thought I am still having the problem...I wanted to vote up to your answer, but unfortunately I don't have enough reputation. Thank you again.

[Brett Caswell]

I had to reread this answer to determine that you did actually provide an answer (I initially stopped reading when I caught this line 'It is actually very difficult/impossible as you said to create 1:1 in SQL').. this was entirely too verbose.. why did you leave the portion where you mistook the question ( which was almost enitrely commentary in the first place)?