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);
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;
This works. The following doesn't:
insert into tq84_t values (1, 'c');
commit;
It fails with
ORA-12008: error in materialized view refresh path
ORA-02290: check constraint (META.CHECK_MAX_2_REGISTRATIONS) violated
You can't use check constraints on subqueries for this. You can, however, use triggers. A simple use of row-level trigger on a table selecting from the same table and raising an exception on count > 4 would result in ORA-4901, so you'd need composite triggers for this. But that is a bit of overkill already.
So, I suggest that you might use a separate table with aggregated counts of user_ids updated via trigger and check-constrain that count. Source code:
create table user_aggr
(
user_id integer not null primary key,
user_count integer not null check (user_count <= 4)
);
You'll need to create a foreign key reference to the user_aggr table on your user table (named user_registration in my example):
create table user_registration
(
registration_id integer primary key,
user_id integer references user_aggr
);
... or just creating the FK constraint ...
alter table user_registration
add constraint FK_user_registration (user_id)
references user_aggr;
... plus the necessary index over the FK ...
create index user_registration_i0
on user_registration (user_id);
Then you'll need the calculation trigger:
create or replace trigger auto_user_counter
after insert or delete or update of user_id
on user_registration
for each row
begin
if updating or deleting then
merge into user_aggr T
using dual
on ( T.user_id = :old.user_id )
when matched then
update set T.user_count = T.user_count - 1
delete where T.user_count = 0
;
end if;
if inserting or updating then
merge into user_aggr T
using dual
on ( T.user_id = :new.user_id )
when matched then
update set T.user_count = T.user_count + 1
when not matched then
insert (user_id, user_count) values (:new.user_id, 1)
;
end if;
end;
/
And now you're all set and ready to rumble. Examples ...
insert into user_registration (registration_id, user_id) values(1, 1); -- OK
insert into user_registration (registration_id, user_id) values(2, 1); -- OK
insert into user_registration (registration_id, user_id) values(3, 1); -- OK
insert into user_registration (registration_id, user_id) values(4, 1); -- OK
insert into user_registration (registration_id, user_id) values(5, 1); -- ERROR
insert into user_registration (registration_id, user_id) values(11, 2); -- OK
insert into user_registration (registration_id, user_id) values(12, 2); -- OK
insert into user_registration (registration_id, user_id) values(13, 2); -- OK
insert into user_registration (registration_id, user_id) values(14, 2); -- OK
insert into user_registration (registration_id, user_id) values(15, 2); -- ERROR
delete from user_registration where user_id = 2 and rownum <= 1; -- OK; user_id 1: 4 rows; user_id 2: 3 rows
update user_registration set user_id = 2 where user_id = 1 and rownum <= 1; -- OK; user_id 1: 3 rows; user_id 2: 4 rows
update user_registration set user_id = 2 where user_id = 1 and rownum <= 1; -- ERROR on user_id 4
You cannot use constraints in this case but you may create an updatable view with check option:
drop table t;
drop view v;
create table t (counter number);
create view v as select * from t where (select count(*) from t) <= 2 with check option;
insert into v values(1); -- OK
insert into v values(2); -- OK
insert into v values(3); -- OK
insert into v values(4); -- ERROR
You may adapt this example for your case.
An updatable view is view which can be treated as an ordinary table. You can create such a view with CHECK OPTION in this case Oracle will prevent you from executing DML on this view which don't relevant to the WHERE clause for this view.
