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
CodePudding user response:
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);
CodePudding user response:
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.