Create restrictions on multiple columns based on the value of the column

Question

I am trying to create a restriction on the oracle database which says the following:

If column1 == someValue , then the combination of column2 and column3 must be unique for all records with column1 == someValue

I am familiar with the concepts of unique and control constraints, and I tried to express a constraint with these constructs. However, I cannot find a way to include the condition. That is why I wonder if this is possible.

The table in which I want to create a constraint is created by Hibernate, displaying the following class hierarchy (most attributes are short for brevity):

 class MyClass { String name; MyClass parent; } class MySubClass extends MyClass { String businessValue; } 

Classes are displayed using a single table strategy and use different discriminator values ​​for each type. The client’s requirement is that for all instances of MySubClass combination of name and parent must be unique (column1 will be the value of the discriminator). It would be easy to provide such a constraint for the parent class through a table constraint. However, this restriction should only apply to MySubClass .

It is possible to validate data before entering it in a database with frameworks such as Hibernate Validator. But since validation will require access to the database anyway, limiting the database seems to be a more efficient way to do this in terms of performance.

Answer 1

You cannot do this with a restriction, but you can do it with a "function-based index" (FBI) as follows:

 create unique index mytable_idx on mytable ( case when column1 = 'somevalue' then column2 end , case when column1 = 'somevalue' then column3 end ); 

This only creates unique index entries for rows with column1 = 'somevalue', so other rows may contain duplicates, but they cannot.