sql - Check constraint on Oracle compare sum to other table value -

i using oracle 11.2 db , have 3 tables:

project_employee

 |id (pk) | p_id |e_id  |month   |capacity|  |--------|------|------|--------|--------|  |1       |1     |1     |201701  |0.4     |  |1       |1     |2     |201701  |0.6     |  |1       |2     |1     |201701  |0.4     | 

employee

 |id (pk)   | maxcapacity   |  |----------|---------------|  |1         | 0.8           |  |2         | 0.6           | 

project

|id (pk)   |other columns|  |----------|-------------| |1         |some data    |  |2         |some data    |  

furthermore have check constraint check wether combination (p_id, e_id. month) of table project_employee unique.

now not want can insert data table project_employee, if sum of capacity employee in 1 month greater maxcapacity specific employee of table employee.

e.g. in example above: should not able insert row 201701 employee 1 nor 2.

is prossible solve check constraint?

a check constraint able check values in same row - cannot perform aggregations - asking not possible.

instead should create procedure handle business logic , revoke permissions perform direct inserts/updates on table ensure procedures used:

create package projects_pkg   procedure add_project_employee(     project_id  in  project_employee.p_id%type,     employee_id in  project_employee.e_id%type,     month       in  project_employee.month%type,     capacity    in  project_employee.capacity%type,     status      out varchar2   ); end; /  create package body projects_pkg   procedure add_project_employee(     i_project_id  in  project_employee.p_id%type,     i_employee_id in  project_employee.e_id%type,     i_month       in  project_employee.month%type,     i_capacity    in  project_employee.capacity%type,     o_error       out varchar2   )       v_current_capacity project_employee.capacity%type;     v_max_capacity employee.maxcapacity%type;   begin     select sum( capacity )       v_current_capacity       project_employee      e_id  = i_employee_id     ,    month = i_month;      select maxcapacity       v_max_capacity       employees      e_id = i_employee_id;      if v_current_capacity + i_capacity > v_max_capacity       o_error := 'max capacity exceeded';       return;     end if;      insert project_employees(       id,       p_id,       e_id,       month,       capacity,     ) values (       project_employees_seq.nextval,       i_project_id,       i_employee_id,       i_month,       i_capacity     );      o_error := null;   exception     when no_data_found       null; -- handle errors   end; end; / 

wiki