how do you restrict number of rows for a particular value
in a column.For example:there is a table called
fruits,having apples,bananas ,papayas.I dont want to have
more than 100 apples in that table ,so how can u restrict
number of rows for apple to hundred?
Answers were Sorted based on User's Feedback
Answer / gk
We can use Check constraint to restict the values of any
column.
Is This Answer Correct ? | 12 Yes | 3 No |
Answer / suresh babu
create check constraint is the best way to restrict the
limited number of entries,because the constraint won't take
a separate space,but Trigger required separate memory
space.perhaps the trigger got error,it would affect all
entries in table.
Is This Answer Correct ? | 2 Yes | 1 No |
Answer / bikash khuntia
We have to create a trigger for that which is one of the
way for the solutuion as below:-
create or replace trigger trigger_name
before insert on table_name
for each row
DECLARE
v_count number;
begin
select count(apple) into v_count from table_name;
if v_count=100 then
raise_application_error('-20011','u cant insert more than
100 apples in the table');
end if;
end;
Is This Answer Correct ? | 1 Yes | 1 No |
Answer / ashwin
You can create a sequence named seq_fruit as below .
create sequence seq_fruit
increment by 1
start with 1
maxvalue 100
minvalue 1
cycle
cache 10
if the current count ie currval of seq_fruit is 100 and
after this if u r inserting a new row in fruit table as
following
insert into fruit values(seq_fruit.nextval,30,40);
after the execution of this statement next value for apple
will be entered as 1 and not 101.
The cycle keyword will avoid the count of fruit column to go
beyond 100.
Is This Answer Correct ? | 0 Yes | 1 No |
What is trigger price?
what is collation? : Sql dba
What is serial sql?
what are the properties and different types of sub-queries? : Sql dba
Why are sql stored procedures used?
what is unique key constraint? : Sql dba
How do you truncate?
how to create a test table in your mysql server? : Sql dba
What are all different types of collation sensitivity?
How does sql profiler work?
how to rename an existing column in a table? : Sql dba
Initially question was asked to mention the types of indexes. Then asked about BITMAP INDEX and B-Tree Index