如何在账户与多Profile的一对多关系中定义主记录？

方案1：使用部分唯一索引（推荐） #

去掉account表中的primary_profile_id字段，转而在profile表添加is_primary布尔字段，通过部分唯一索引强制每个账户下只能有一个主profile，再用触发器确保每个账户至少保留一个主profile。

表结构定义 #

create table account (
    id         uuid          primary key,
    email      text          unique,
    phone      text          unique,
    created_at timestamptz
);

create table profile (
    id           uuid          primary key,
    account_id   uuid          references account on delete cascade,
    username     text          unique,
    about        text,
    created_at   timestamptz,
    is_primary   boolean       default false
);

-- 部分唯一索引：限制每个account_id仅能对应一个is_primary=true的记录
create unique index idx_profile_primary_per_account 
on profile (account_id) 
where is_primary = true;

操作流程 #

• 注册环节：先插入account记录，再插入第一个profile并将is_primary设为true，符合索引约束规则。
• 切换主profile：在同一事务内，先将原主profile的is_primary改为false，再将目标profile的is_primary设为true，避免触发唯一索引冲突。

确保每个账户至少有一个主profile #

通过触发器防止删除或取消最后一个主profile：

-- 检查账户是否仍有主profile的函数
create or replace function check_account_has_primary_profile()
returns trigger as $$
begin
    if not exists (
        select 1 from profile 
        where account_id = old.account_id and is_primary = true
    ) then
        raise exception '账户必须保留至少一个主profile';
    end if;
    return old;
end;
$$ language plpgsql;

-- 删除主profile前执行检查
create trigger trigger_profile_delete_primary
before delete on profile
for each row
when (old.is_primary = true)
execute function check_account_has_primary_profile();

-- 取消主profile状态前执行检查
create trigger trigger_profile_update_primary
before update of is_primary on profile
for each row
when (old.is_primary = true and new.is_primary = false)
execute function check_account_has_primary_profile();

方案2：保留primary_profile_id，用延迟约束解决循环依赖 #

如果必须在account表保留primary_profile_id字段，可以使用PostgreSQL的延迟约束，允许在事务内先插入数据再补全关联，最终保证约束生效。

表结构定义 #

create table account (
    id                   uuid          primary key,
    email                text          unique,
    phone                text          unique,
    created_at           timestamptz,
    primary_profile_id   uuid          references profile on delete restrict deferrable initially deferred
);

create table profile (
    id           uuid          primary key,
    account_id   uuid          references account on delete cascade,
    username     text          unique,
    about        text,
    created_at   timestamptz
);

插入流程（需在同一事务内执行） #

1. 插入account记录，primary_profile_id暂时设为null
2. 插入关联该account的profile记录
3. 更新account的primary_profile_id为刚插入的profile的id
4. 提交事务，此时延迟约束会自动检查primary_profile_id的有效性

额外约束 #

确保primary_profile_id不为空且关联的profile属于当前账户：

-- 强制primary_profile_id不能为null
alter table account add constraint chk_account_primary_profile_not_null 
check (primary_profile_id is not null);

-- 检查主profile是否属于当前账户的触发器
create or replace function check_primary_profile_belongs_to_account()
returns trigger as $$
begin
    if not exists (
        select 1 from profile 
        where id = new.primary_profile_id and account_id = new.id
    ) then
        raise exception '主profile必须属于当前账户';
    end if;
    return new;
end;
$$ language plpgsql;

create trigger trigger_account_primary_profile
before insert or update on account
for each row
execute function check_primary_profile_belongs_to_account();

方案对比 #

• 方案1更简洁，彻底避免循环依赖，用索引直接实现核心约束，适合大多数业务场景。
• 方案2保留了account到主profile的直接引用，适合需要快速通过account获取主profile的场景，但要严格遵守事务内的操作顺序。

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