仅允许将3行添加到表中以获取特定值 [英] Allow only 3 rows to be added to a table for a specific value

问题描述

我手头有一个问题，我需要将分配给经理的项目数限制为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?

推荐答案

如何实现对0.3的限制?"

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

这需要一个断言，该断言在SQL标准中定义，但未在Oracle中实现. (尽管有对其进行介绍的举动.)

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
/

魔术是:

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.

由于mview在提交时(即以事务方式)刷新，因此您需要在project表上建立日志:

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

这篇关于仅允许将3行添加到表中以获取特定值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！