oracle-database

how can i limit count of duplicate rows in oracle?

create table file( member_no number, filepath varchar2(100) );

I want to limit the number of duplicate rows of member_no in this table. for example:

enter image description here

In this way, the number of member_no can be up to 5, but it shouldn't be more than six. how can I do this?

Solution

  • So you have two ways I can think of: when you are inserting (I assume you are using a stored procedure), run an if to check the current rows:

    Declare 
count number;
too_many_num_exception EXCEPTION;
BEGIN

select count(file_path) into count from file where member_no = <num_you_are_inserting>;

if(count = 5)
Then
    raise too_many_num_exception;
end if;

insert(...);
EXCEPTION
    WHEN too_many_num_exception then
        --do something

end;

    Or you could try play around with creating indexes on you tables (however this may not work - it's just a thought)

    CREATE UNIQUE INDEX file_ix1 on file (
CASE WHEN (select count() from file ... ) < 6 THEN member_id ELSE NULL END) 
online

    Although I'm not 100% if that would work