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.

Can you share the DDL you used to build your tables and constraints? Also, what do you want your query to actually show? It sounds like more of you need a constraint on the branch table that the manager MUST be an employee where position = 'MANAGER' — thatjeffsmith, May 19 '18 at 16:11
@AswaniMadhavan - basically this is a summary of the OP's question rather than a helpful suggestion. — APC, May 19 '18 at 17:20

score 1 · Answer 1 · edited May 19 '18 at 16:38

1

Something like

SELECT * FROM branch 
INNER JOIN employee ON branch.manager_id = employee.employee_id 
WHERE position LIKE "%Manager%"

edited May 19 '18 at 16:38

ScaisEdge

131,976
10
91
107

answered May 19 '18 at 16:16

Chris Fodor

119
21

APC · Accepted Answer · 2018-05-19T18:49:13.637

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.

How to enforce a business rule across database tables?

2 Answers2