Complex Integrity Constraints

- To implement the complex integrity constraints in the oracle table, let us see one of the example
- By creating simple table and test it

create table cons
(a int,
 b int,
 c varchar2(10) not null
);

- To check the uniqueness except the data is not ‘W’ in the column(c).
- Create the unique index such as

CREATE INDEX ind1 ON cons 
( 
CASE 
      when c <> 'W' then a
end,
CASE 
      when c <> 'W' then b
end
);

- To test the scenario

SQL>Insert into cons values(1,1,’W’);

1 row inserted.

SQL>Insert into cons values(1,1,’W’);

1 row inserted.

SQL>Insert into cons values(1,1,’x’);

1 row inserted.

SQL>Insert into cons values(1,1,’y’);

ERROR at line 1:
ORA-00001: unique constraint (BOOK.IND1) violated