如何在Drift(SQLite)中处理可选中间外键关联？

一、调整数据模型结构 #

核心思路是给 Task 表新增非空的 projectId 字段，同时保留可空的 milestoneId，并通过约束保证数据一致性：

1. 表定义代码 #

// Projects 表
class Projects extends Table {
  IntColumn get id => integer().autoIncrement()();
  TextColumn get name => text()();
  // 其他业务字段...
}

// Milestones 表
class Milestones extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get projectId => integer().references(Projects, #id, onDelete: KeyAction.cascade)();
  TextColumn get name => text()();
  // 其他业务字段...
}

// Tasks 表（核心调整）
class Tasks extends Table {
  IntColumn get id => integer().autoIncrement()();
  IntColumn get milestoneId => integer().nullable().references(Milestones, #id, onDelete: KeyAction.setNull)();
  IntColumn get projectId => integer().references(Projects, #id, onDelete: KeyAction.cascade)();
  TextColumn get name => text()();
  // 其他业务字段...

  @override
  List<Constraint> get constraints => [
    // 约束：若关联 Milestone，必须与 Task 同属一个 Project
    CustomConstraint('CHECK (milestoneId IS NULL OR EXISTS (SELECT 1 FROM milestones WHERE id = milestoneId AND projectId = tasks.projectId))'),
  ];
}

模型设计说明 #

• Task.projectId 强制非空，确保每个 Task 始终关联一个 Project
• Task.milestoneId 可空，支持直接关联 Project 的场景
• CHECK 约束避免出现「Task 关联了其他 Project 的 Milestone」的非法数据
• 外键级联操作：删除 Project 时自动删除其下所有 Task；删除 Milestone 时将关联 Task 的 milestoneId 置为 null，保留 Task 与 Project 的关联

二、封装查询逻辑 #

通过 Drift 视图封装重复的 JOIN 逻辑，简化业务层查询：

1. 定义查询视图 #

@DriftView()
abstract class TaskWithDetails extends View {
  Tasks get tasks;
  Projects get projects;
  Milestones get milestones;

  @override
  Query as() => select([
    tasks.id,
    tasks.name,
    projects.id.as('project_id'),
    projects.name.as('project_name'),
    milestones.id.as('milestone_id'),
    milestones.name.as('milestone_name'),
    // 按需添加其他字段
  ])
      .from(tasks)
      .innerJoin(projects, projects.id.equalsExp(tasks.projectId))
      .leftJoin(milestones, milestones.id.equalsExp(tasks.milestoneId));
}

2. DAO 中使用视图 #

@DriftAccessor(tables: [Projects, Milestones, Tasks], views: [TaskWithDetails])
class TasksDao extends DatabaseAccessor<AppDatabase> with _$TasksDaoMixin {
  TasksDao(AppDatabase db) : super(db);

  // 监听所有带关联信息的 Task
  Stream<List<TaskWithDetailsData>> watchAllTasks() {
    return select(taskWithDetails).watch();
  }

  // 根据 Project ID 查询其下所有 Task（含直接关联和通过 Milestone 关联的）
  Stream<List<TaskWithDetailsData>> watchTasksByProject(int projectId) {
    return select(taskWithDetails)
        .where((t) => t.projectId.equals(projectId))
        .watch();
  }
}

三、数据插入优化 #

为避免手动输入错误，封装两种场景的插入方法：

// 插入关联 Milestone 的 Task
Future<int> insertTaskToMilestone(TasksCompanion task, int milestoneId) async {
  final milestone = await (select(milestones)..where((m) => m.id.equals(milestoneId))).getSingle();
  return into(tasks).insert(task.copyWith(
    milestoneId: Value(milestoneId),
    projectId: Value(milestone.projectId),
  ));
}

// 插入直接关联 Project 的 Task
Future<int> insertTaskToProject(TasksCompanion task, int projectId) {
  return into(tasks).insert(task.copyWith(
    projectId: Value(projectId),
    milestoneId: const Value(null),
  ));
}

方案优势 #

• 数据一致性：通过外键和 CHECK 约束彻底避免非法关联
• 查询简洁：视图封装了多表连接逻辑，业务层无需重复编写 JOIN
• 易维护：模型结构清晰，后续修改只需调整约束或视图即可

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