如何在一对多关系约束中限制A被B关联的最大数量为3？

需求规则：

• 一个B只能关联一个A
• 一个A最多只能被3个B关联

现有表结构代码：

create table A (
A_id number constraint Aid_pk primary key);

create table B (
B_id number constraint Bid_pk primary key,
A_id number constraint ref_fk references A(A_id));

这是标准的一对多关联关系，需要修改代码实现上述数量限制。

由于单纯的主键/外键约束无法实现基于计数的限制，我们可以通过触发器来实现这一规则（以Oracle数据库为例）：

1. 保留原有的表创建语句，新增行级触发器，在插入或更新B表的A_id字段时校验关联数量：

CREATE OR REPLACE TRIGGER trg_limit_b_per_a
BEFORE INSERT OR UPDATE OF A_id ON B
FOR EACH ROW
DECLARE
    v_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_count
    FROM B
    WHERE A_id = :NEW.A_id;
    
    IF v_count >= 3 THEN
        RAISE_APPLICATION_ERROR(-20001, '一个A最多只能关联3个B');
    END IF;
END;
/

触发器说明 #

• 触发时机：在B表插入记录，或更新A_id字段之前执行
• 逻辑：统计当前待关联的A_id在B表中已有的关联记录数，若数量已达3，则抛出自定义错误，阻止操作完成
• 覆盖场景：既限制插入新B记录时的关联数量，也限制更新B记录的关联A时不会导致某个A的关联数超过3

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