sql - 一对多关系的最大约束 - Oracle SQL

Question

使用 Orcale SQL 开发人员，我想绘制出员工和经理之间的关系。但是，经理最多只能监督 3 名员工。



上面我有一个员工表，其中经理 ID 作为外键。这与员工表有一对多的关系。

是否可以将这种关系限制为最多 3 个？

谢谢。

Answer 1

这不能通过检查约束来完成。应该可以创建一个物化 View ，该 View 计算每个管理器的出现次数，对计数进行检查约束，并在原始表上提交时刷新。正如 Littlefoot 所演示的那样，可以使用复合触发器实现相同的功能。但这不是很可扩展，因为每次提交后都需要扫描整个表以刷新物化 View 。

一种替代解决方案是:

创建一个新表来跟踪每个经理的出现次数，比如 employee_manager_cnt

在 employee 上设置触发器表保持表employee_manager_cnt最新(不需要扫描整个表，只反射(reflect)基于 manager_id 的新旧值的变化)

向 employee_manager_cnt 添加检查约束禁止高于目标计数的值

这是一个 step by step demo ，其灵感来自 the answer by nop77svk on this SO question

原表:

create table employees (
    employee_id number primary key,
    manager_id number
);

插入几条记录:

begin
    insert into employees values(1, null);
    insert into employees values(2, 1);
    insert into employees values(3, 1);
    insert into employees values(4, 1);    -- manager 1 has 3 employees
    insert into employees values(5, null);
    insert into employees values(6, 5);    -- manager 5 has just 1 employee
end;
/

创建新表:

create table employee_manager_cnt (
    manager_id          number not null primary key,
    cnt                 number(1, 0) not null check (cnt <= 3)
);

填充它:

insert into employee_manager_cnt(manager_id, cnt)
select manager_id, count(*) 
from employees 
where manager_id is not null
group by manager_id

检查结果:

MANAGER_ID  CNT
1           3
5           1

现在，创建触发器:

create or replace trigger trg_employee_manager_cnt
    after insert or delete or update of manager_id
    on employees
    for each row
begin

    -- decrease the counter when an employee changes manager or is removed
    if updating or deleting then
        merge into employee_manager_cnt t
        using dual
        on ( t.manager_id = :old.manager_id )
        when matched then
            update set t.cnt = t.cnt - 1
            delete where t.cnt = 0
        ;
    end if;

    -- increase the counter when a employee changes manager or is added
    if inserting or updating then
        merge into employee_manager_cnt T
        using dual
        on ( t.manager_id = :new.manager_id )
        when matched then
            update set t.cnt = t.cnt + 1
        when not matched then
            insert (manager_id, cnt) values (:new.manager_id, 1)
        ;
    end if;
end;
/

现在尝试添加引用经理 1(已经有 3 名员工)的新记录

insert into employees values(4, 1);
-- error: ORA-00001: unique constraint (FIDDLE_QOWWVSAIOXRDGYREFVKM.SYS_C00276396) violated

同时，仍然有可能将一名新员工影响到经理 5(他只有一名员工):

insert into employees values(10, 5);
-- 1 rows affected