One to zero or one relationship using composite key and database first

Question

I'm failing at creating a 1 to 0..1 relationship between two tables, where the primary key is a composite key consisting of two columns.

The tables are imported to the EF with an many to many relationship, changing the relationship to 1 to 0..1 or 1 to 1 results in the following error:

Multiplicity is not valid in Role Because the Dependent Role properties are not the key properties, the upper bound of the multiplicity of the Dependent Role must be *

Primary table:

CREATE TABLE [dbo].[MeasurementBlobs] (
    [MeasurementResultId]   INT NOT NULL,
    [ValueType]             SMALLINT NOT NULL,
    [Id]                    UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL,
    [Value]                 VARBINARY(MAX) FILESTREAM NOT NULL,
    CONSTRAINT [PKMeasurementBlobs] PRIMARY KEY CLUSTERED ([MeasurementResultId], [ValueType]),
    CONSTRAINT [FKMeasurementBlobsMeasurementResults] FOREIGN KEY ([MeasurementResultId]) REFERENCES [dbo].[MeasurementResults] ([Id]),
    CONSTRAINT [UQMeasurementBlobsId] UNIQUE ([Id])
)
GO

Foreign key table:

CREATE TABLE [dbo].[MeasurementBlobsMeasurementClusters]
(
    [MeasurementResultId]   INT NOT NULL,
    [ValueType]             SMALLINT NOT NULL,
    [MeasurementClusterId]  INT NOT NULL,
    CONSTRAINT [PKMeasurementBlobsMeasurementClusters] PRIMARY KEY CLUSTERED ([MeasurementResultId], [ValueType]  ASC, [MeasurementClusterId] ASC),
    CONSTRAINT [FKMeasurementBlobsMeasurementClustersMeasurementBlob] FOREIGN KEY ([MeasurementResultId], [ValueType]) REFERENCES [dbo].[MeasurementBlobs] ([MeasurementResultId], [ValueType]),
    CONSTRAINT [FKMeasurementBlobsMeasurementClustersMeasurementCluster] FOREIGN KEY ([MeasurementClusterId]) REFERENCES [dbo].[MeasurementClusters] ([Id])
)
GO

Using table table per type inheritance is not an option.

Model first assumes you are using an edmx to generate the database; given that you have SQL here and have stated the tables were imported into EF, this looks to be Database First. Also, what do you mean when you say "The tables were imported with a Many to Many relationship"? I don't see a 3rd table that would be necessary in Many to Many? — Claies, May 04 '15 at 14:37
"changing the relationship to 1 to 0..1 or 1 to 1" How do you do that? Please show the mapping. — Gert Arnold, May 04 '15 at 15:00
@Claies of course your right, changed the title to database first. Well, there is no 3rd table, they are imported that way. — Michael Sander, May 04 '15 at 15:50

John Bollinger · Accepted Answer · 2015-05-04T16:07:56.117

1

Your database structure is incorrect for the relationship multiplicity you describe. Table MeasurementBlobsMeasurementClusters may contain multiple rows having the same (MeasurementResultId, ValueType), therefore that side of the relationship must have type *. Place a uniqueness constraint on the FK to allow this side to have multiplicity 0..1.

The other side of the relationship relies on the PK of table MeasurementBlobs, which of course is unique in that table. Although perhaps you can model that side as "many", you should model it as 1. If you want to assign the multiplicity of this side as 0..1, then in the other table you must make the foreign key referencing this table be nullable.

Update: better even than placing a separate uniqueness constraint on the FK in MeasurementBlobsMeasurementClusters would be to remove the MeasurementClusterId column from its PK, leaving only the two columns of the FK. If the FK must indeed be unique in the table, then it is a perfectly suitable PK by itself. Of course, in that case the question arises of why you need to model cluster <-> measurement blob relationship with a separate table instead of with a direct FK relationship.

edited May 04 '15 at 16:07

answered May 04 '15 at 15:26

John Bollinger

160,171
8
81
157

I already tried the unique constraint on the foreign key, that does not help. I can also not set the foreign key to nullable, as its the primary key of the foreign table at the same time. Maybe that's the real issue which is confusing the EF. – Michael Sander May 04 '15 at 15:52
Why is `MeasurementClusterId` part of the PK of its table if the other two columns are sufficient to uniquely identify a row? Or if the other two are *not* sufficient to uniquely identify a row, then that is equivalent to the EF's complaint. – John Bollinger May 04 '15 at 16:01
Your EF has specific expectations for how your database will be structured to model various relationship multiplicities, and apparently it needs 1:1 and 1:0..1 relationships to go through entities' PKs, not merely through an unique FK. I have updated my answer with another alternative based on my previous comment. – John Bollinger May 04 '15 at 16:10
The problem was indeed the the primary key of the the foreign key table as it was composed of an an additional column. Thank you for the help! – Michael Sander May 05 '15 at 08:54

score 0 · Answer 2 · answered May 05 '15 at 08:56

The problem was caused by the primary key of the foreign key table.

The primary key was composed of three columns, one more than needed by the foreign key. Here is the working foreign key table definition:

CREATE TABLE [dbo].[MeasurementBlobsMeasurementClusters]
(
    [MeasurementResultId]   INT NOT NULL,
    [ValueType]             SMALLINT NOT NULL,
    [MeasurementClusterId]  INT NOT NULL,
    [IsFaultSource]         BIT NOT NULL,
    CONSTRAINT [PKMeasurementBlobsMeasurementClusters] PRIMARY KEY CLUSTERED ([MeasurementResultId], [ValueType]),
    CONSTRAINT [FKMeasurementBlobsMeasurementClustersMeasurementBlob] FOREIGN KEY ([MeasurementResultId], [ValueType]) REFERENCES [dbo].[MeasurementBlobs] ([MeasurementResultId], [ValueType]),
    CONSTRAINT [FKMeasurementBlobsMeasurementClustersMeasurementCluster] FOREIGN KEY ([MeasurementClusterId]) REFERENCES [dbo].[MeasurementClusters] ([Id])
)

One to zero or one relationship using composite key and database first

2 Answers2