Having trouble creating a one to 'zero or one' relationship using Entity Framework

Question

I have two tables:

CREATE TABLE Order (
    orderId INTEGER IDENTITY NOT NULL,
    PRIMARY KEY (orderId) 
)

CREATE TABLE OrderAdditionalDetails (
    additionalDetailsId INTEGER IDENTITY NOT NULL,
    orderId INTEGER NOT NULL,
    PRIMARY KEY (additionalDetailsId),
    FOREIGN KEY (orderId) REFERENCES Order(orderId)
)

I have a Foreign key (FK_OrderAdditionalDetails_Order) declared on the OrderAdditionalDetails table, on the orderId field. I also have a 'unique' constraint on the orderId field in the OrderAdditionalDetails table. The idea is that each 'order' will have zero or one entries in the 'OrderAdditionalDetails' table.

This all picked up by the entity framework model file, however when I try to create the Navigation property, it only lets me declare a 1 to many relationship. The error I get is as follows:

Running transformation: Multiplicity is not valid in Role 'OrderAdditionalDetails' in relationship 'FK_OrderAdditionalDetails_Order'. Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be *.

I'm really not sure what this means - googling the error did not prove helpful. Can anybody shed some light on what I am doing wrong?

ErikE · Answer 1 · 2012-12-27T09:42:49.447

In your OrderAdditionalDetails table, remove the additionalDetailsID column and make the orderID the CLUSTERED PRIMARY KEY. Keep the FOREIGN KEY you already have. That is the right way to implement this.

There is not only no value added by the additionalDetailsId column, it makes things worse by taking more space in the table. The orderID is already a sufficient key; you need no secondary artificial key that is nothing but a surrogate for orderID.

score 1 · Answer 2 · edited May 23 '17 at 11:48

1

Your Foreign Key must be defined as UNIQUE in order to enforce a One-To-Zero-Or-One relationship.

Maybe try something like this:

CREATE TABLE OrderAdditionalDetails (
    additionalDetailsId INTEGER IDENTITY NOT NULL,
    orderId INTEGER NOT NULL UNIQUE,
    PRIMARY KEY (additionalDetailsId),
    FOREIGN KEY (orderId) REFERENCES Order(orderId)
)

See Also: Implementing one-to-zero-or-one relation in SQL Server

edited May 23 '17 at 11:48

Community

1
1

answered Jul 20 '12 at 21:47

Nate

30,286
23
113
184

I tried this, and in theory this should have worked, but it didn't... Using EF5 (DB first with Models) and SQLServer 2012 – Rob Nov 01 '13 at 14:10

score 0 · Answer 3 · answered Nov 03 '14 at 16:41

I was trying to associate a table with a view of itself plus some other fields. (There is a very good reason for this that has nothing to do with the answer)

What cause the same error was there was more than one key field on the view. Even though I had specified the fields involved in the association it wanted both to be the only key fields for a 1 to 1 to work.

I also set the key field to be Distinct in the view, but I did that before I removed the key attribute of other fields, so it may ,or may not, be necessary.

Having trouble creating a one to 'zero or one' relationship using Entity Framework

3 Answers3