I have a table say xyz which contains a field say roll_number, now I want to implement a check so that any roll number is allowed only twice in xyz.
OR
Suppose I have a table xyz containing roll numbers, and another table abc containg roll numbers (unique) and class(eg - 1, 2, 3 etc), now I want that only roll number of class 3 is allowed only twice in xyz table and rest can be allowed any number of times.
Is it even possible??
If yes, please help out.
Thanks.
Yes, but they're all fiddly.
You could add a counter column to the table. Place incrementing values in this. Then have a:
- check constraint to ensure it's only 1 or 2 (if class = 3)
- unique index to ensure roll_number and counter are unique (if class = 3)
create table t (
class int,
roll int,
roll_ct int not null
);
create unique index u on t (
case when class = 3 then roll end,
case when class = 3 then roll_ct end
);
alter table t add constraint c check (
case when class = 3 then roll_ct end in (1, 2)
);
insert into t values (1, 1, 1);
insert into t values (1, 1, 2);
insert into t values (1, 1, 3);
insert into t values (3, 1, 1);
insert into t values (3, 1, 2);
insert into t values (3, 1, 3);
SQL Error: ORA-00001: unique constraint (CHRIS.U) violated
Or you could create a materialized view. This counts how many values there are per roll (if class = 3). Then add a check constraint to ensure this count is always < 3. Ensure the MV is "refresh on commit" and Oracle will reject transactions that result in more than 2 values/roll number at commit time:
drop index u;
alter table t drop constraint c;
truncate table t;
create materialized view log on t
with rowid, sequence (class, roll) including new values;
create materialized view mv
refresh fast on commit
as
select roll, count(*) c from t
where class = 3
group by roll;
alter table mv add constraint ck check (c < 3);
insert into t values (1, 1, 1);
insert into t values (1, 1, 1);
insert into t values (1, 1, 1);
commit;
insert into t values (3, 1, 1);
insert into t values (3, 1, 1);
insert into t values (3, 1, 1);
commit;
SQL Error: ORA-12008: error in materialized view refresh path
There are other things you could do, but these are variations on the themes above.
If this all sounds complicated, we have a proposal for you: SQL assertions. With these you can base a constraint on a query. e.g.:
create assertion at_most_two as check
((select count(*) from t
where class = 3) <= 2
)
But we're still looking for community support for this feature. If you'd like us to implement assertions, head to the ideas page and vote it up:
https://community.oracle.com/ideas/13028 Every vote helps!