Bug: conflicting constraints on polymorphic mappings

[mindplay]

I have a number of subclasses in a table-per-inheritance-hierarchy scenario.

Some of these subclasses have a navigation property, referencing another entity - each subclass references a different type of entity.

To consolidate the schema, I use a single column called EntityId - and each of the subtypes map their navigation-properties to that same column.

In this particular case, one subtype references a Contact entity, while another subtype references a Role entity - and the generated constraints are as follows:

Code: Select all

      CONSTRAINT FK_TaskAssociation_Contact_1 FOREIGN KEY (EntityId) REFERENCES dbo.Contact (Id),
      CONSTRAINT FK_TaskAssociation_Role_2 FOREIGN KEY (EntityId) REFERENCES dbo.Role (Id)

Obviously you can't enforce multiple constraints in this way, as they conflict with each other. In fact, you probably can't (or at leastshould not) enforce any referential constraints in cases where multiple conflicting constraints would apply.

(this may come back to a problem I've highlighted on multiple occasions - the fact that each of these distinct navigation-properties, despite mapping to the same physical foreign-key column in the database, have their own unique column-specifications...)

[Shalex]

Could you please send us a small test model and specify the steps to reproduce the problem?

[mindplay]

Here's an example:

https://docs.google.com/open?id=0B_rcns ... k3OTg4MzFh

And here's the schema output:

Code: Select all

-- 
-- Creating a table dbo.Groups 
-- 
CREATE TABLE dbo.Groups (
   Id INT NOT NULL IDENTITY,
   CONSTRAINT PK_Groups PRIMARY KEY (Id)
)
GO

-- 
-- Creating a table dbo.Contacts 
-- 
CREATE TABLE dbo.Contacts (
   Id INT NOT NULL IDENTITY,
   CONSTRAINT PK_Contacts PRIMARY KEY (Id)
)
GO

-- 
-- Creating a table dbo.Assignments 
-- 
CREATE TABLE dbo.Assignments (
   Id INT NOT NULL IDENTITY,
   AssignmentDiscriminator INT NOT NULL,
   EntityId INT NOT NULL,
   CONSTRAINT PK_Assignments PRIMARY KEY (Id),
   CONSTRAINT FK_Assignments_Contacts_0 FOREIGN KEY (EntityId) REFERENCES dbo.Contacts (Id),
   CONSTRAINT FK_Assignments_Groups_1 FOREIGN KEY (EntityId) REFERENCES dbo.Groups (Id)
)
GO

As you can see, conflicting constraints on the Assignments.EntityId column.

This example would have a many-to-many mapping between Group and Contact, but I didn't model that, as it has no particular relevance to the problem illustrated by this example.

[Shalex]

We are investigating the issue.

[mindplay]

A note about implementing a solution to this problem: Make sure that this does not affect the generated mappings.

When it comes to the mappings, you do want the type-specific constraints to be enforced by NH as defined.

In other words, part of the problem here, is that what you've defined as Column settings for a discriminator, is in fact something that is (in part) specific to the mappings, and not just to the schema.

That is, you may have one sub-type that must have an EntityId value value (not-null="true"), as well as another sub-type that has an optional Entity value (not-null="false").

At the schema-level, that means your only option is to leave the EntityId-column nullable - but at the mapping-level, you're better of having NH validate the constraints correctly, e.g. with not-null="true" for the one type, and not-null="false" for the other.

[mindplay]

Actually, come to think of it, this comes back to the problem we discussed earlier, where I asked you to validate that the column-settings are identical for any properties/discriminators that map to the same column.

Apparently, that's not necessarily correct. You may in fact have to establish a convention or "lowest common denominator" for certain column-settings, when generating the schema.

For example, if one set of column-settings says not-null="true", and the other says not-null="false", the lowest common denominator would be not-null="false", since you can't enforce the constraint at the schema-level anymore.

As said, you can (should) still enforce constraints at the mapping-level, so be careful how you implement this.

For certain column-settings, errors should be reported when there's a discrepancy - not all column-settings can be reconciled. For example, I don't believe there's any (practical) way to reconcile conflicting column-types, such as INT and VARCHAR.

You have to carefully consider each column-setting and decide whether you can establish a logical convention for reconciling conflicting column-settings; as opposed to throwing an error-message during validation.

[Shalex]

The merging of crossing foreign key columns in Table Per Hierarchy (TPH) when generating schema is added. We will post here when the corresponding build of Entity Developer is available for download.

[Shalex]

New version of Entity Developer 4.3 is released!
It can be downloaded from http://www.devart.com/entitydeveloper/download.html (trial version) or from Registered Users' Area (for users with active subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=23647 .