I have a Oracle table which has 10 columns - col1, col2, col3....col10. My requirement is - For a unique set of values of col1, col2, col3 i.e, (col1, col2, col3) only a unique value for col4 is allowed even if it is multiple times. Ex: If there are 10 rows which have col1=3, col2='A005' and col3=10, then for all the 10 rows the value of col4 should be same.
How can i put this restriction in Oracle?
you could make a unique constraint on the three columns (co1, col2, col3) and then use that as a foreign key into a separate table (i.e. move col4 to a different table). This will eliminate the duplicate values you will currently get in col4 (which is bad practice - read up on 'normalization' of databases to see why).
So - for every unique combination (col1, col2, col3) it will map to exactly one row for col4 - but in a separate table.
E.g. in sqlfiddle : http://sqlfiddle.com/#!4/ad317
EDIT : modifying table columns / schema not desired
You can add triggers to your main table, to write the values/changes into a separate 'link' table : http://sqlfiddle.com/#!4/d78f6
The second, 'link' table enforces uniqueness of col4 for each unique value of (col1, col2, col3). This allows you to keep the columns / values of table1 the same as before - basically duplicating the information into a table where you do have control to normalize it.
