如何使用oracle检查约束来限制注册数量? [英] How to use oracle check constraints to limit number of registration?
本文介绍了如何使用oracle检查约束来限制注册数量?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个具有唯一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屋!
查看全文