I have inherited a database where the structure is like this:

class class Address
{
    public int AddressId {get;set;}
    public int AddressType {get;set;} // 1 = user, 2 = group
}

class User
{
   public int UserId {get;set;}
}
class Group
{
   public int GroupId {get;set;}
}
class UserAddress : Address
{
    public User {get;set;}
    public int UserId {get;set;}
}
class GroupAddress : Address
{
    public Group {get;set;}
    public int GroupId {get;set;}
}

I’m trying to wire up EF (the code originally used Dapper, but is migrating to EF, for ‘reasons’).

I added a discriminator like this:

public void Configure(EntityTypeBuilder<Address> builder)
{
    builder
        .HasDiscriminator(x => x.AddressType)
        .HasValue<UserAddress>(1)
        .HasValue<GroupAddress>(2);
}

Also, configured so the groupId/userId use the same underlying column (instanceId).

I’ve now got a migration which looks OK, but when I run the migration, I get errors like this:

Cannot add or update a child row: a foreign key constraint fails (XXXX.#sql-3c6c_17, CONSTRAINT FK_address_user_userId FOREIGN KEY (instanceId) REFERENCES user (userId) ON DELETE CASCADE)

The error is correct because there is some data where the instanceId column relates to a group, and others where it relates to a user. Surely the discriminator code should generate a constraint that has some sort of filtering (sql check)? e.g. if addressType=1, add to FK_address_user_userId, if addressType=2, add to FK_address_group_groupId.

generated SQL

ALTER TABLE `address` ADD CONSTRAINT `FK_address_user_userId` 
FOREIGN KEY (`instanceId`) 
REFERENCES `user` (`userId`) ON DELETE CASCADE;

• MySql: 8
• .NET/EF: 6