Skip to Main Content
  • Questions
  • Contraint/Check _ Prevent any value to be inserted more than twice in any table.

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Pawan.

Asked: August 02, 2016 - 7:07 am UTC

Last updated: August 02, 2016 - 4:04 pm UTC

Version: 11.2.0.1.0

Viewed 1000+ times

You Asked

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.

and Chris said...

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!

Is this answer out of date? If it is, please let us know via a Comment