Using a Function Based Index to enforce complex unique constraints

Sometimes there is a need to enforce different rules for uniqueness depending on
the type of record. Using a Function Based Index is one way to accomplish this.
 
EXAMPLE:

Lets start with a preview of our table

PRIM_KEY A REC_CLASS           DLR_FK DLR_SER_FK WHERE_CLAUSE         FIELD_FK ---------- - --------------- ---------- ---------- ------------------ ----------         70 Y PRODUCT                          2202 number-Floors=2          1111         80 Y PRODUCT                          2202 number_floors = 1        1111         90 N DEALER_SERVICE                   2202                          3333        100 Y DEALER_SERVICE                   2202                          3333        110 Y DEALER                1000                                     3333         10 Y DEALER                1000                                     1111         20 Y DEALER                1000                                     2222         30 Y DEALER_SERVICE                   2202                          1111         40 N DEALER_SERVICE                   2202                          1111         50 Y PRODUCT                          2200 number_floors = 1        1111         60 Y PRODUCT                          2200 number-Floors=2          1111 Figure 1. Uniqueness

Figure 1 shows in red the columns that must be unique for each of the three types of
records. Each type of record requires the the following combinations be unique.

 

1. DEALER_SERVICE
1. Active_Flag
2. DEALER_SER2DEALER_SERVICE
3. PLAYING_FIELD2PLAYING_FIELD  
2. DEALER
1. DEALER2DEALER
2. PLAYING_FIELD2PLAYING_FIELD
3. PRODUCT
1. none
   
   
   

DROP TABLE LIMITS_AND_TOTALS; CREATE TABLE LIMITS_AND_TOTALS (   LIMITS_AND_TOTALS_KEY         NUMBER(10)      NOT NULL,   ACTIVE_FLAG                   VARCHAR2(1)     DEFAULT 'Y' NOT NULL,   REC_CLASS                     VARCHAR2(30)    NOT NULL,   CAP_LIMIT                     NUMBER(10)      NOT NULL,   CURRENT_CNT                   NUMBER(10)      NOT NULL,   DEALER2DEALER                 NUMBER(10),   DEALER_SER2DEALER_SERVICE     NUMBER(10),   WHERE_CLAUSE                  VARCHAR2(4000 BYTE),   PLAYING_FIELD2PLAYING_FIELD   NUMBER(10) ); ALTER TABLE LIMITS_AND_TOTALS ADD (   CONSTRAINT LIMITS_AND_TOTALS_PK  PRIMARY KEY  (LIMITS_AND_TOTALS_KEY)); --ALTER TABLE LIMITS_AND_TOTALS ADD ( --  CONSTRAINT DEALER_FK -- FOREIGN KEY (DEALER2DEALER) -- REFERENCES DEALER (DEALER_KEY) --    ); --ALTER TABLE LIMITS_AND_TOTALS ADD ( --  CONSTRAINT DEALER_SER_FK -- FOREIGN KEY (DEALER_SER2DEALER_SERVICE) -- REFERENCES DEALER_SERVICE (DEALER_SERVICE_KEY) --    ); --ALTER TABLE LIMITS_AND_TOTALS ADD ( --  CONSTRAINT PLAYING_FIELD_FK -- FOREIGN KEY (PLAYING_FIELD2PLAYING_FIELD) -- REFERENCES PLAYING_FIELD (PLAYING_FIELD_KEY)); CREATE OR REPLACE FUNCTION flimit_unique (    rec_class               VARCHAR2  , dealer_key              INTEGER  , dealer_service_key      INTEGER  , playing_field_key       INTEGER  , active_flag             VARCHAR2  , limits_and_totals_key   INTEGER )    RETURN VARCHAR2 DETERMINISTIC AS    class_str           CHAR ( 30 );    key_str             CHAR ( 10 );    playing_field_str   CHAR ( 10 );    unique_str          CHAR ( 10 );    result_str          VARCHAR2 ( 90 );    active_str          CHAR ( 1 ); BEGIN    class_str := rec_class;    -- copy the playing field FK or use 0 (zero) in the even of null    playing_field_str := COALESCE ( TO_CHAR ( playing_field_key ), '0' );    active_str := active_flag;    IF    class_str = 'PRODUCT'       OR (     class_str = 'DEALER_SERVICE'            AND active_str = 'N' )    THEN         -- append the primary key       unique_str := TO_CHAR ( limits_and_totals_key );   --         -- doing this forces the generation of a unique index key         -- so THIS will always be unique    ELSE       unique_str := 0;    END IF;    -- pick the FK that is relevant to the current REC_CLASS type.    -- Only one of these FKs will have a non null value    key_str := COALESCE ( TO_CHAR ( dealer_key ), TO_CHAR ( dealer_service_key ) );    result_str :=          RPAD ( RTRIM ( class_str ), 30, '*' )       || LPAD ( RTRIM ( key_str ), 10, '#' )       || LPAD ( RTRIM ( playing_field_str ), 10, '+' )       || LPAD ( RTRIM ( unique_str ), 10, '@' );    RETURN result_str; END; / CREATE UNIQUE INDEX limits_and_totals_ix ON limits_and_totals (flimit_unique ("REC_CLASS",                       "DEALER2DEALER",                       "DEALER_SER2DEALER_SERVICE",                       "PLAYING_FIELD2PLAYING_FIELD",                       "ACTIVE_FLAG",                       "LIMITS_AND_TOTALS_KEY")); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer_ser2dealer_service, where_clause, playing_field2playing_field             )      VALUES ( 70, 'Y', 'PRODUCT', 4, 1             , 2202, 'number-Floors=2', 1111             ); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer_ser2dealer_service, where_clause, playing_field2playing_field             )      VALUES ( 80, 'Y', 'PRODUCT', 5, 2             , 2202, 'number_floors = 1', 1111             ); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer_ser2dealer_service, playing_field2playing_field             )      VALUES ( 90, 'N', 'DEALER_SERVICE', 22, 5             , 2202, 3333             ); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer_ser2dealer_service, playing_field2playing_field             )      VALUES ( 100, 'Y', 'DEALER_SERVICE', 14, 3             , 2202, 3333             ); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer2dealer, playing_field2playing_field             )      VALUES ( 110, 'Y', 'DEALER', 20, 6             , 1000, 3333             ); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer2dealer, playing_field2playing_field             )      VALUES ( 10, 'Y', 'DEALER', 25, 5             , 1000, 1111             ); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer2dealer, playing_field2playing_field             )      VALUES ( 20, 'Y', 'DEALER', 20, 6             , 1000, 2222             ); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer_ser2dealer_service, playing_field2playing_field             )      VALUES ( 30, 'Y', 'DEALER_SERVICE', 14, 3             , 2202, 1111             ); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer_ser2dealer_service, playing_field2playing_field             )      VALUES ( 40, 'N', 'DEALER_SERVICE', 22, 5             , 2202, 1111             ); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer_ser2dealer_service, where_clause, playing_field2playing_field             )      VALUES ( 50, 'Y', 'PRODUCT', 5, 2             , 2200, 'number_floors = 1', 1111             ); INSERT INTO limits_and_totals             ( limits_and_totals_key, active_flag, rec_class, cap_limit, current_cnt             , dealer_ser2dealer_service, where_clause, playing_field2playing_field             )      VALUES ( 60, 'Y', 'PRODUCT', 4, 1             , 2200, 'number-Floors=2', 1111             ); COMMIT ;

SET DEFINE OFF;
col where_clause format a18
col rec_class format a15

SELECT limits_and_totals_key prim_key, active_flag af, rec_class
     , dealer2dealer dlr_fk, dealer_ser2dealer_service dlr_ser_fk, where_clause
     , playing_field2playing_field field_fk
  FROM limits_and_totals;

PRIM_KEY A REC_CLASS           DLR_FK DLR_SER_FK WHERE_CLAUSE         FIELD_FK ---------- - --------------- ---------- ---------- ------------------ ----------         70 Y PRODUCT                          2202 number-Floors=2          1111         80 Y PRODUCT                          2202 number_floors = 1        1111         90 N DEALER_SERVICE                   2202                          3333        100 Y DEALER_SERVICE                   2202                          3333        110 Y DEALER                1000                                     3333         10 Y DEALER                1000                                     1111         20 Y DEALER                1000                                     2222         30 Y DEALER_SERVICE                   2202                          1111         40 N DEALER_SERVICE                   2202                          1111         50 Y PRODUCT                          2200 number_floors = 1        1111         60 Y PRODUCT                          2200 number-Floors=2          1111 Figure 1. Uniqueness

First we try to insert a duplicate DEALER record.

INSERT INTO LIMITS_AND_TOTALS    (LIMITS_AND_TOTALS_KEY, ACTIVE_FLAG, REC_CLASS, CAP_LIMIT, CURRENT_CNT,    DEALER2DEALER, PLAYING_FIELD2PLAYING_FIELD)  VALUES    (150, 'Y', 'DEALER', 20, 6, 1000, 3333); COMMIT; ORA-00001: UNIQUE CONSTRAINT (LIMITS_AND_TOTALS_IX) violated

Try changing the active flag TO 'N'

INSERT INTO LIMITS_AND_TOTALS    (LIMITS_AND_TOTALS_KEY, ACTIVE_FLAG, REC_CLASS, CAP_LIMIT, CURRENT_CNT,    DEALER2DEALER, PLAYING_FIELD2PLAYING_FIELD)  VALUES    (150, 'N', 'DEALER', 20, 6, 1000, 3333); COMMIT; ORA-00001: UNIQUE CONSTRAINT (LIMITS_AND_TOTALS_IX) violated

The problem is still the same.. Remember, on DEALER records, active flag does NOT
matter for the purpose of uniqueness.

Next, try the same thing with REC_CLASS = DEALER_SERVICE

INSERT INTO LIMITS_AND_TOTALS    (LIMITS_AND_TOTALS_KEY, ACTIVE_FLAG, REC_CLASS, CAP_LIMIT, CURRENT_CNT,    DEALER_SER2DEALER_SERVICE, PLAYING_FIELD2PLAYING_FIELD)  VALUES    (140, 'Y', 'DEALER_SERVICE', 14, 3, 2202, 3333); COMMIT; ORA-00001: UNIQUE CONSTRAINT (LIMITS_AND_TOTALS_IX) violated

AS expected, that DEALER_SERVICE for that PLAYING_FIELD already exists.

Now lets see what happens if we set the active flag to 'N'

INSERT INTO LIMITS_AND_TOTALS    (LIMITS_AND_TOTALS_KEY, ACTIVE_FLAG, REC_CLASS, CAP_LIMIT, CURRENT_CNT,     DEALER_SER2DEALER_SERVICE, PLAYING_FIELD2PLAYING_FIELD)  VALUES    (140, 'N', 'DEALER_SERVICE', 14, 3, 2202, 3333); COMMIT;

This time, no Error! This is because we allow any number of DEALER_SERVICE type
records provided that the active flag = 'N'.