Oracle: Function Based Index Selective Uniqueness

Oracle: function based index selective uniqueness

You would need something like

CREATE UNIQUE INDEX fn_unique_idx 
    ON table1 (CASE WHEN is_deleted='N' THEN id ELSE null END,
               CASE WHEN is_deleted='N' THEN name ELSE null END,
               CASE WHEN is_deleted='N' THEN type ELSE null END);

An example of the constraint in action

SQL> create table table1 (
  2    id number,
  3    name varchar2(10),
  4    type varchar2(10),
  5    is_deleted varchar2(1)
  6  );

Table created.

SQL> CREATE UNIQUE INDEX fn_unique_idx
  2      ON table1 (CASE WHEN is_deleted='N' THEN id ELSE null END,
  3                 CASE WHEN is_deleted='N' THEN name ELSE null END,
  4                 CASE WHEN is_deleted='N' THEN type ELSE null END);

Index created.

SQL> insert into table1 values( 1, 'Foo', 'Bar', 'N' );

1 row created.

SQL> insert into table1 values( 1, 'Foo', 'Bar', 'Y' );

1 row created.

SQL> insert into table1 values( 1, 'Foo', 'Bar', 'Y' );

1 row created.

SQL> insert into table1 values( 1, 'Foo', 'Bar', 'N' );
insert into table1 values( 1, 'Foo', 'Bar', 'N' )
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.FN_UNIQUE_IDX) violated

SQL> insert into table1 values( 1, 'Foo', 'Zee', 'N' );

1 row created.

Function Based Index based on a column

The purpose of index is to list all entries in an easy and fast to access manner.
Suppose you get a 1500 pages thick book to read with an index listing only specific chapters. What is the use of such an index?
I don't think you need a function based index here. Simply create an index and modify your queries to include IS_LOGGED = 'Y' clause.

Enforce Uniqueness on a Subset of Rows on Table in Oracle DB

You can create a unique function-based index to enforce uniqueness of (make,model) pairs only when make='Ford':

create table cars (make varchar2(255) not null, model varchar2(255) not null);

create unique index ford_index on cars (
  case make 
  when 'Ford' then make
  else null
  end,
  case make 
  when 'Ford' then model
  else null
  end  );

insert into cars (make, model) values ('Ford', 'F150');
insert into cars (make, model) values ('Ford', 'Fiesta');
insert into cars (make, model) values ('Toyota', 'Camry');
insert into cars (make, model) values ('Toyota', 'Camry');
insert into cars (make, model) values ('Ford', 'F150'); --- FAILS with unique constraint violation

ford_index indexes (make, model) only if make='Ford' - everything else is left out of the index and so is not subject to the uniqueness constraint.

Conditional Unique Constraint, Ignored for Same Foreign Key

An interesting problem. I would approach this in the following way:

CREATE TABLE t123_fk(
  FK_1 int,
  FK_2 int,
  CONSTRAINT t123_fk_pk PRIMARY KEY(FK_1, FK_2),
  CONSTRAINT fk_2_is_new_constr_violated UNIQUE(FK_1)
);

CREATE TABLE t123(
  ID int,
  FK_1 int,
  FK_2 int,
  NAME varchar2(100),
  constraint t123_fk FOREIGN KEY(FK_1, FK_2) REFERENCES t123_fk
);

CREATE OR REPLACE TRIGGER some_name 
BEFORE INSERT OR UPDATE ON t123
FOR EACH ROW
BEGIN
  INSERT INTO t123_fk( fk_1, fk_2 )
  SELECT :new.FK_1, :new.FK_2 FROM dual
  WHERE NOT EXISTS(
     SELECT 1 FROM t123_fk
     WHERE fk_1 = :new.fk_1 AND fk_2 = :new.fk_2
  );
END;
/

Table t123 is the main table containing our data.

Table t123_fk and the trigger are auxiliary and they are used only to help us to force our constraint (btw, our constraint is named fk_2_is_new_constr_violated in the above code).

---

Here is a test - the fourth insert will be rejected by the database:

insert into t123( id, fk_1, fk_2, name) values(1,2,3,'X01');
insert into t123( id, fk_1, fk_2, name) values(2,2,3,'X01-A');
insert into t123( id, fk_1, fk_2, name) values(3,2,3,'X01');
insert into t123( id, fk_1, fk_2, name) values(4,2,4,'X01'); // this insert will be rejected
insert into t123( id, fk_1, fk_2, name) values(5,3,5,'X01');

Not able to use in Function based Indexes in Oracle

You could use the result of that comparison in a case statement to come up with an actual value, with a supported data type, rather than a boolean - which has been noted already isn't supported y Oracle as an SQL data type. What that value is doesn't really matter as long as you're consistent; you could use Y/N, 0/1, etc.

Depending on your data spread and selectivity of how you'll query, you could use a bitmap index:

create bitmap index ix_test_table on test_table
  (case when nvl(col_a, 0) <> nvl(col_b, 0) then 1 else 0 end);

And then query on the same case, of course:

select * from test_table
where case when nvl(col_a, 0) <> nvl(col_b, 0) then 1 else 0 end = 1;
select * from test_table
where case when nvl(col_a, 0) <> nvl(col_b, 0) then 1 else 0 end = 0;

Or if it's very selective only include the small subset of rows that you're interested in by utilising the fact that null values are not included in the index:

create index ix_test_table on test_table
  (case when nvl(col_a, 0) <> nvl(col_b, 0) then 1 end);

select * from test_table
where case when nvl(col_a, 0) <> nvl(col_b, 0) then 1 end = 1;

You'd need to evaluate which is appropriate for your data.

How to configure unique constraint that has an exception?

You can't code that logic in a constraint.

You can create a unique function-based index, however, which probably lets you accomplish the same task

CREATE UNIQUE INDEX idx_my_table
    ON my_table ( CASE WHEN a <= b THEN a ELSE NULL END,
                  CASE WHEN a <= b THEN b ELSE NULL END,
                  CASE WHEN a <= b THEN c ELSE NULL END );

This takes advantage of the fact that Oracle doesn't store completely NULL values in the index structure. This allows you to have as many rows where b < a as you'd like and none of them would be stored in the index structure.

---

Related Topics