stop duplicates when one part of the same record exists

Question

i have a table called registry and i have a couple of columns, for this example i am only going to state four(4) columns:-

• r_id
• file_number
• file_name
• volume

now these columns support duplicate filenumbers and filename but for each duplicate there needs to be a different volume. for eg.

r_id              filenumber            filename             volume
1                  123                   test                  1
2                  123                   test                  2
3                  234                   oracle                1
4                  234                   oracle                2
5                  123                   test                  1

now the issue is r_id 5 which has a duplicate volume as r_id 1.

so my question is how can i stop a duplicate volume when a record with the same file_number , file_name and volume exist?.

Nb.I am using the regular insert statement to create the records

Answer 1

You can add a unique constraint on columns filenumber, filename, volume:

alter table table_name
add constraint fnumber_fname_vol_unique unique(filenumber, filename, volume);

Answer 2

If you want to prevent duplicate inserts see @Zakaria. If you want to ensure unique volume values, you could try along:

insert into data(r_id, filenumber, filename, volume)
  select
    data_sequence.nextval as r_id,
    123 as filenumber,
    'test' as filename,
    coalesce((select max(volume) + 1 from data where filenumber = 123 and filename = 'test'), 1) as volume
   from dual
;

See it in action: SQL Fiddle.

Please comment, if and as this requires adjustment / further detail.