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
user_registration runs just fine. Inserting the same user into user_registration in 2 different sessions causes the 2nd session to wait due to the 1st session having placed a lock on the respective user_aggr row. After committing the 1st session the 2nd session's merge resumes, does its work and the check constraint is properly evaluated. Anyway, I'll be happy to talk to you (via Skype), if possible, so we might resolve this interesting quarrel. –
Jugglery :old.user_id and :new.user_id rows at the same time via single select for update at the start of the trigger (the if updating ... branch would be sufficient) or by writing the two merges as one working over both IDs (somehow). –
Jugglery 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.
check option clause! I was thinking about writing a trigger for this, but your solution is much more elegant (although error-prone when inserting directly to the table). –
Jugglery insert into v select * from table(sys.ora_mining_number_nt(1,2,3,4,5,6,7,8,9,10)); :-( –
Jugglery © 2022 - 2024 — McMap. All rights reserved.