Search code examples
sqldatabaseoracle-databasefunctionconstraints

Constraint on Column Based on Another Column Value

I'm writing a sql constraint about values on columns based on some conditions in Oracle database. My table is like below.(assume id is auto increment, also 'alpha' and 'beta' columns are numbers)

   id    alpha    beta
--------------------------
   1      1        0
   2      1        1
   3      0        0
   4      0        0
   5      2        3
   6      4        1

If alpha value in two rows are same, only one row can be inserted with beta value of 1. In other words, i shouldn't insert a row with (1,1) values because there is already a row with beta value of 1.(look at the row with id=2). Any value besides 1 can be inserted freely. I need a useful control about that situation.

Solution

  • You can use unique index with condition-based column as follows:

    create unique index u123
on your_table (alpha, case when beta = 1 then beta else id end)