如何使用oracle检查约束来限制注册数量? [英] How to use oracle check constraints to limit number of registration?

问题描述

我有一个具有唯一user_id的用户表.用户可以使用那里的ID注册.现在我想限制最大.使用CHECK约束为每个用户注册.所以我用这个:

I've a user table with unique user_id. User can register using there id. Now I want to limit the max. registration per user using CHECK constraints. so I use this:

.... CHECK(select count(user_id) from user where ... ...)

但是这表明子查询不能在检查约束中使用.

But it's show subquery cannot use in check constraints.

有人可以告诉我如何添加此条件吗?

Can anyone tell me how can I add this condition?

推荐答案

在某些条件下，您可以使用物化视图来强制执行表限制:

Under certain conditions, you can enforce table restrictsion with materialized views:

create table tq84_t (
  user_id   number,
  foo       varchar2(10),
  constraint pk_tq84_t primary key (user_id, foo)
);

create materialized view log on tq84_t;

create materialized view tq84_mv 
 refresh on commit
as
  select user_id, count(*) cnt
    from tq84_t
   group by user_id;

alter table tq84_mv
  add constraint check_max_2_registrations 
  check (cnt < 3);

使用此实例化视图，Oracle在提交时检查对实例化视图的约束:

With this materialized view, Oracle checks the constraint on the materialized view when you commit:

insert into tq84_t values (1, 'a');
insert into tq84_t values (1, 'b');

commit;

这有效.以下不是:

insert into tq84_t values (1, 'c');

commit;

失败并

ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (META.CHECK_MAX_2_REGISTRATIONS) violated

这篇关于如何使用oracle检查约束来限制注册数量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！