create unique index with case ... when statement : Create Index « Index « Oracle PL/SQL Tutorial






  1. Oracle PL/SQL Tutorial
  2. Index
  3. Create Index
SQL> create table registrations
  2  ( attendee    NUMBER(4)
  3  , course      VARCHAR2(6)
  4  , begindate   DATE
  5  , evaluation  NUMBER(1)
  6  , constraint  R_PK        primary key(attendee,course,begindate)
  7  ) ;

Table created.

SQL>
SQL> insert into registrations values (8,'JAV',date '2009-12-13',5   );

1 row created.

SQL> insert into registrations values (9,'JAV',date '2009-12-13',4   );

1 row created.

SQL> insert into registrations values (4,'JAV',date '2000-02-01',3   );

1 row created.

SQL> insert into registrations values (8,'JAV',date '2000-02-01',4   );

1 row created.

SQL> insert into registrations values (6,'JAV',date '2000-02-01',5   );

1 row created.

SQL> insert into registrations values (12,'XML',date '2000-02-03',4   );

1 row created.

SQL> insert into registrations values (2,'XML',date '2000-02-03',5   );

1 row created.

SQL> insert into registrations values (4,'PLS',date '2000-09-11',NULL);

1 row created.

SQL> insert into registrations values (2,'PLS',date '2000-09-11',NULL);

1 row created.

SQL> insert into registrations values (11,'PLS',date '2000-09-11',NULL);

1 row created.

SQL>
SQL> create unique index oau_reg on registrations
  2  ( case course when 'OAU' then attendee else null end
  3  , case course when 'OAU' then course   else null end );

Index created.

SQL>
SQL> insert into registrations values (12,'OAU',sysdate,null);

1 row created.

SQL>
SQL> drop index oau_reg;

Index dropped.

SQL> drop table registrations;

Table dropped.








9.1.Create Index
9.1.1.Creating an Index
9.1.2.Enforce uniqueness of values in a column using a unique index
9.1.3.Create combined-column index
9.1.4.Create a composite index on multiple columns
9.1.5.Creating a Function-Based Index
9.1.6.Create index based on cluster
9.1.7.Creates an index on the new added column
9.1.8.Create a fully indexed table named myCode
9.1.9.indextype is ctxsys.context
9.1.10.Create index for upper case last name
9.1.11.Create index along with the column definition
9.1.12.Create index for combined columns
9.1.13.Create unique index and check it in user_ind_columns and user_cons_columns
9.1.14.Demonstrate a bitmap join index.
9.1.15.create unique index with case ... when statement
9.1.16.Create Non-Unique index
9.1.17.autotrace ctxsys.context index
9.1.18.Create index with tablespace