3

I have a question in hand where i need to restrict the number of projects assigned to a manager to only 3. The tables are:

Manager:
Manager_employee_id(PK)
Manager_Bonus

Project:
project_number(PK)
Project_cost
Project_manager_employee_id(FK)

Can anyone suggest what approach to take to implement this?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Jamie Schaffer
  • 31
  • 1
  • Some suggestion: 1. Write a function F to check for it, and call F when you INSERT/ UPDATE `project` table. 2. Use trigger on `project` table. – Pham X. Bach Nov 14 '17 at 07:44
  • You can also do this : Instead of putting a `0, n` relationship between the `Manager` and `Project` table, you can restrict to `0,3`. – F0XS Nov 14 '17 at 07:45
  • @foxcy - that is how to model the constraint. The OP is asking how to **implement** such a constraint. – APC Nov 14 '17 at 07:46
  • How do I implement the restrict to 0,3? – Jamie Schaffer Nov 14 '17 at 07:47
  • This question is not clear. Do you mean exactly 3, or at most three? Please edit your question, don't clarify in comments. – philipxy Feb 02 '18 at 20:18

3 Answers3

3

"How do I implement the restrict to 0,3?"

This requires an assertion, which is defined in the SQL standard but not implemented in Oracle. (Although there are moves to have them introduced).

What you can do is use a materialized view to enforce it transparently.

create materialized view project_manager
refresh on commit 
as 
select Project_manager_employee_id
        , count(*) as no_of_projects
from project
group by Project_manager_employee_id
/

The magic is:

alter table project_manager
   add constraint project_manager_limit_ck check 
       ( no_of_projects <= 3 )
/

This check constraint will prevent the materialized view being refreshed if the count of projects for a manager exceeds three, which failure will cause the triggering insert or update to fail. Admittedly it's not elegant.

Because the mview is refreshed on commit (i.e. transactionally) you will need to build a log on project table:

create materialized view log on project
APC
  • 144,005
  • 19
  • 170
  • 281
0

I would do the following:

  1. Create new column projects_taken (tinyint) (1) (takes values of 1,2 or 3) with default value of 0.
  2. When manager takes project, the field will increment by 1
  3. Do simple checks (through the UI) to see if the field projects_taken is equal or smaller than 3.
Matthias Seifert
  • 2,033
  • 3
  • 29
  • 41
Gospot Bog
  • 1
  • 2
  • 2
    So sad you fell at the final hurdle. If we go to the trouble of adding and populating a column why wouldn't we build a check constraint on it to enforce the limit? The lessons of history are that relying on an application to enforce business rules inevitably leads to data corruption. – APC Nov 14 '17 at 08:03
0

I would do the following:

  1. Create one SP for both operation insert/update.
  2. Check with IF not exists Project_manager_employee_id(FK) count < 3 then only proceed for insert/update. otherwise send Throw error.
Dinesh
  • 112
  • 8