How to enforce a business rule across database tables?

Question

I am working on this BANK MANAGEMENT SYSTEM database project (oracle) on sql developer and I have made some progress but I am stuck at this minor problem

The problem: I have a table of employee with employee id as primary key and in that table there is an attribute of position (clerk, manager etc), and branch id (the branch at which that employee works) which is a foreign key (referenced to branch_id of branch table). Then there is a table of branch with branch id as primary key and manager_id as foreign key (referenced to the employee_id of employee table).

What kind of query should I write to ensure that the employee who is the manager of that branch also must have the term 'Manager" as his/her position.

Answer 1

The most straightforward way is to build a trigger. This one looks up the MANAGER_ID in the EMPLOYEE to valid their position (I have also added a rule that the manager must be an employee of the branch they manage, perhaps you don't need this).

create trigger branch_manager_trg 
     before insert or update on branch_manager   
     for each row
declare
    dummy employee.employee_id%type;
begin
    select e.employee_id
    into dummy
    from employee e
    where e.employee_id = :new.manager_id
    and e.branch_id = :new.branch_id
    and e.emp_type = 'MANAGER';
exception
  when no_data_found then
       raise_application_error(-20000, 'Branch manager must be a MANAGER');
end;
/

Here is a LiveSQL demo (free Oracle Dev Community account required).

One thing you want to think about is, what should happen if the Employee record of the branch manager is updated? If they are no longer a MANAGER or change branch?

---

Incidentally, I added a table to your data model: you need a intersection table between EMPLOYEE and BRANCH to hold the Branch Manager record. Otherwise you will have a cyclic dependency between EMPLOYEE.BRANCH_ID -> BRANCH.BRANCH_ID and BRANCH.MANAGER_ID -> EMPLOYEE.EMPLOYEE_ID.