如何定义SQL Server表结构，确保Identifier仅被单次引用？

1. 数据库层面约束设置 #

要确保Identifier表的每条记录只能被Lecturers或Contractors其中一个表引用一次，需通过约束组合实现强限制：

步骤1：修改Identifier表结构 #

新增两个字段用于标记引用关系：

• EntityType：固定值为'Lecturer'或'Contractor'，标识当前记录归属的实体类型
• EntityId：存储对应的讲师ID或承包商ID

修改后表结构：

步骤2：添加唯一约束 #

保证同类型实体不会重复引用同一个Identifier：

ALTER TABLE Identifier
ADD CONSTRAINT UQ_Identifier_EntityType_EntityId UNIQUE (EntityType, EntityId);

步骤3：添加检查与筛选外键约束 #

• 检查约束：限制EntityType的可选值，同时确保EntityId不为空

ALTER TABLE Identifier
ADD CONSTRAINT CK_Identifier_EntityType CHECK (EntityType IN ('Lecturer', 'Contractor'));

ALTER TABLE Identifier
ADD CONSTRAINT CK_Identifier_EntityId_NotNull 
CHECK (
    (EntityType = 'Lecturer' AND EntityId IS NOT NULL) 
    OR (EntityType = 'Contractor' AND EntityId IS NOT NULL)
);

• 筛选外键（SQL Server 2016+支持）：根据EntityType关联到对应实体表

-- 关联讲师表
ALTER TABLE Identifier
ADD CONSTRAINT FK_Identifier_Lecturers 
FOREIGN KEY (EntityId) REFERENCES Lecturers(lecturer_id)
WHERE EntityType = 'Lecturer';

-- 关联承包商表
ALTER TABLE Identifier
ADD CONSTRAINT FK_Identifier_Contractors 
FOREIGN KEY (EntityId) REFERENCES Contractors(contractor_id)
WHERE EntityType = 'Contractor';

注：若使用SQL Server旧版本，可通过触发器替代筛选外键逻辑。

步骤4：调整实体表约束 #

给Lecturers和Contractors的identifier字段添加唯一约束，确保一个实体最多关联一个Identifier：

ALTER TABLE Lecturers
ADD CONSTRAINT UQ_Lecturers_Identifier UNIQUE (identifier);

ALTER TABLE Contractors
ADD CONSTRAINT UQ_Contractors_Identifier UNIQUE (identifier);

同时保留原外键关联Identifier表：

ALTER TABLE Lecturers
ADD CONSTRAINT FK_Lecturers_Identifier 
FOREIGN KEY (identifier) REFERENCES Identifier(identifier_id);

ALTER TABLE Contractors
ADD CONSTRAINT FK_Contractors_Identifier 
FOREIGN KEY (identifier) REFERENCES Identifier(identifier_id);

2. EF Core模型调整 #

重新执行Scaffold-DbContext生成模型后，手动调整实体关系：

• 删除Identifier实体自动生成的Lecturers/Contractors集合属性
• 添加一对一导航属性并配置关系：

public class Identifier
{
    public int identifier_id { get; set; }
    public int identifier { get; set; }
    public string identifierString { get; set; }
    public string EntityType { get; set; }
    public int EntityId { get; set; }

    public Lecturer Lecturer { get; set; }
    public Contractor Contractor { get; set; }
}

public class Lecturer
{
    public int lecturer_id { get; set; }
    public int? identifier { get; set; }

    public Identifier Identifier { get; set; }
}

public class Contractor
{
    public int contractor_id { get; set; }
    public int? identifier { get; set; }

    public Identifier Identifier { get; set; }
}

在DbContext的OnModelCreating中补充配置：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 配置一对一关系
    modelBuilder.Entity<Identifier>()
        .HasOne(i => i.Lecturer)
        .WithOne(l => l.Identifier)
        .HasForeignKey<Lecturer>(l => l.identifier)
        .IsRequired(false);

    modelBuilder.Entity<Identifier>()
        .HasOne(i => i.Contractor)
        .WithOne(c => c.Identifier)
        .HasForeignKey<Contractor>(c => c.identifier)
        .IsRequired(false);

    // 同步数据库检查约束
    modelBuilder.Entity<Identifier>()
        .HasCheckConstraint("CK_Identifier_EntityType", "EntityType IN ('Lecturer', 'Contractor')");

    // 同步唯一约束
    modelBuilder.Entity<Identifier>()
        .HasIndex(i => new { i.EntityType, i.EntityId })
        .IsUnique();
}

3. 业务逻辑辅助验证 #

在C#代码层添加前置校验：

• 分配标识前检查目标Identifier是否已被引用
• 确保单个实体不会重复绑定多个标识

内容的提问来源于stack exchange，提问作者SoulB