Press "Enter" to skip to content

Limiting Max Records Insertion In A Table Without Trigger In Oracle

Suppose you have a requirement that a particular table in Oracle can not have records greater than 10 and also there is a requirement to handle this issue without trigger.
Then there is only one way left from my point of view is Check Constraint for a particular field, for this case you must have a field to control or you have to create it a new one.
You can control easily on a number field if getting inserted eg. any id field where a unique id is getting saved on every record commit, but if not then you have to create it.
Example Table:
create table max_10_rec
(
srlno number(2) primary key check(srlno between 1 and 10),
ename varchar2(30)
)
/
In above example the srlno field will handle the max insertion of 10 records, because it has a primary key and a check constraint for checking the value from 1 to 10.