CODEWITHSUNDEEP
c#sqlsql-serverentity-frameworkef-model-first
Michael Sander
Michael Sander

Reputation: 2737

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

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.

Upvotes: 0

Views: 1259

Answers (2)

Michael Sander
Michael Sander

Reputation: 2737

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])
)

Upvotes: 0

John Bollinger
John Bollinger

Reputation: 180161

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.

Upvotes: 1

Related Questions