Maximum Constraint on One to Many Relationship - Oracle SQL

This cannot be done with a check constraint. It should be possible to create a materialized view that count the number of occurences of each manager, with a check constraint on the count, and that refreshes on commit on the original table. The same can be implemented with a compound trigger, as demonstrated by Littlefoot. But this is not very scalable, since the whole table needs be scanned to refresh the materialized view after each commit.

One alternative solution would be to:

• create a new table that keeps track of the number of occurences of each manager, say employee_manager_cnt

• set up a trigger on the employee table to keep table employee_manager_cnt up to date (no need to scan the whole table, just reflect the changes based on the old and new value of manager_id)

• add a check constraint to the employee_manager_cnt that forbids values above the target count

Here is a step by step demo, which is inspired by the answer by nop77svk on this SO question

Original table:

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

Insert a few records:

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 employeeend;/

Create the new table:

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

Populate it:

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

Check the results:

MANAGER_ID  CNT1           35           1

Now, create the trigger:

create or replace trigger trg_employee_manager_cnt    after insert or delete or update of manager_id    on employees    for each rowbegin    -- 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;/

Now try to add a new record that references manager 1 (who already has 3 employees)

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

Meanwhile it is still possible to affect a new employee to manager 5 (he just has one employee):

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

In order to find number of employees per manager in the table, you have to count them, right? But, if you do that, you'll hit the mutating table error as you can't select from a table which is currently being changed.

Nowadays, we fix that using the compound trigger. Here's an example:

Sample table:

SQL> create table temp  2    (empid number primary key,  3     name  varchar2(20),  4     mgrid number references temp (empid)  5    );Table created.

Compound trigger:

SQL> create or replace trigger trg_3emp  2    for update or insert on temp  3    compound trigger  4  5    type emprec is record (mgrid temp.mgrid%type);  6    type row_t  is table of emprec index by pls_integer;  7    g_row_t     row_t;  8  9  after each row is 10  begin 11    g_row_t (g_row_t.count + 1).mgrid := :new.mgrid; 12  end after each row; 13 14  after statement is 15    l_cnt number; 16  begin 17    for i in 1 .. g_row_t.count loop 18      select count(*) 19      into l_cnt 20      from temp 21      where mgrid = g_row_t(i).mgrid; 22 23      if l_cnt = 4 then 24         raise_application_error(-20000, 'No more than 3 employees per manager'); 25      end if; 26    end loop; 27  end after statement; 28  end; 29  /Trigger created.SQL>

Testing:

SQL> -- This will be the managerSQL> insert into temp values (1, 'Little', null);1 row created.SQL> -- Next 3 rows will be OKSQL> insert into temp values (2, 'Foot'  , 1);1 row created.SQL> insert into temp values (3, 'Scott' , 1);1 row created.SQL> insert into temp values (4, 'Tiger' , 1);1 row created.SQL> -- The 4th employee for the same manager should failSQL> insert into temp values (5, 'Mike'  , 1);insert into temp values (5, 'Mike'  , 1)            *ERROR at line 1:ORA-20000: No more than 3 employees per managerORA-06512: at "SCOTT.TRG_3EMP", line 22ORA-04088: error during execution of trigger 'SCOTT.TRG_3EMP'SQL> -- Someone else can be Mike's managerSQL> insert into temp values (5, 'Mike', 2);1 row created.SQL>

Maybe with a trigger.

create or replace trigger constraint_triggerbefore insert on employee DECLARE    x number;begin    select count(*) into x from employee where manager_id=:new.manager_id;    if (x=3) then        raise your_exeption;    end if;end;