0

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?

Imad Alazani
  • 6,688
  • 7
  • 36
  • 58
Rama Kodam
  • 1

1 Answers1

1

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.

Graham Griffiths
  • 2,196
  • 1
  • 12
  • 15
  • I hope my requirement is not confusing. Let me clarify again. Multiple entries for a set of (col1, col2, col3, col4) are allowed. But for a set of (col1, col2, col3) only one value of col4 is allowed. An insertion with a distinct value should error out. Let us assume that currently there are no rows where col1=3 and col2='A005' and col3=10. An insertion is done with the above values and col4=234. Subsequent insertions with col1=3 and col2='A005' and col3=10 should have col4=234 only. Other values for col4 are not permissible. – Rama Kodam Aug 05 '13 at 12:49
  • let me check my understanding : so any time that col1, col2 and col3 are the same, then col4 must also be the same. So, although you will allow separate rows, you want the same value of col4 to be repeated? – Graham Griffiths Aug 05 '13 at 12:53
  • if so - my point is that this seems like a common bad practice, and the usual way to solve it is to remove col4 to a separate table, where we can avoid having duplicate values. Duplicate values cause problems when reading, when writing, when modifying...more detail on this best practice 'normalization' is here : http://stackoverflow.com/questions/2331838/normalization-in-plain-english – Graham Griffiths Aug 05 '13 at 12:55
  • Yes Graham. An insertion with a different value should error out. – Rama Kodam Aug 05 '13 at 12:57
  • just for example, problems caused by duplicate values. Say you want to modify col4 for one row. To maintain your integrity, you will have to go and look for any other rows that have the same values (col1, col2, col3). Instead, if it is in a separate table with no duplicates - you can just update a single row without worrying. – Graham Griffiths Aug 05 '13 at 12:58
  • my suggested structure will error out if inserting a different value for col4 - it will give a 'foreign key constraint violated' error on the second insert – Graham Griffiths Aug 05 '13 at 12:59
  • Actually, i cannot modify the table. Modifying the table will result in changes of code in many applications. Please let me know if it can be done through CHECK constraint or a Trigger. – Rama Kodam Aug 05 '13 at 13:04
  • it might be worth updating the question to say it is not possible to modify the table / columns, since it will make a big difference to the possible solutions. – Graham Griffiths Aug 05 '13 at 13:23