CODEWITHSUNDEEP
oracle-database
Coding Duchess
Coding Duchess

Reputation: 6909

Creating a conditional index based on a column value

Table1
___________
User_ID
Location_ID
Type_ID
Level_ID

Levels
_________
1111    Manager
2222    Staff

There is an existing constraint on User_ID and Category_ID. I need to add a constraint to ensure that only one user of level Manager can be added to a location for each type.

How do I go about creating a unique index on Location_ID, Type_ID and Level_ID where level_id=mypackage.get_level_id('Manager')?

I am trying something like that:

CREATE UNIQUE INDEX idx_mgr on Table1 (CASE WHEN Level_id=mypackage.get_level_id('Manager') 
                    THEN (Location_ID,Type_ID)
                    ELSE NULL
                END);

or

CREATE UNIQUE INDEX idx_mgr ON (DECODE(Level_ID,mypackage.get_level_id('Manager'),NULL);

How do I get something like this to work?

Upvotes: 0

Views: 163

Answers (1)

Marmite Bomber
Marmite Bomber

Reputation: 21075

Defining a function index for the management level only (referencing teh constant directly - not via package) you get the required behaviour:

create unique index ix1 on table1
 (case when level_id = 1111 then Location_ID end, case when level_id = 1111 then Type_ID end);

You can insert any number of stuff with the same location and type, but only one manager:

insert into table1(User_ID,Location_ID, Type_ID, Level_ID) values(1,1,1,2222); 
insert into table1(User_ID,Location_ID, Type_ID, Level_ID) values(2,1,1,2222);
insert into table1(User_ID,Location_ID, Type_ID, Level_ID) values(3,1,1,1111);
insert into table1(User_ID,Location_ID, Type_ID, Level_ID) values(4,1,1,1111);
-- fails with ORA-00001: Unique Constraint violated

Upvotes: 1

Related Questions