1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Unique constraints with function based index and novalidate

Discussion in 'SQL PL/SQL' started by sneha.bharti, Apr 19, 2016.

  1. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    Hi All,

    I have been trying to put a unique constraint with some conditions and having no validate clause..
    As we can't put any condition in unique constraint directly, I made a function based normal index since because of duplicate records i can not make unique index, now i want to create an unique constraint using this index with novalidate clause, but it's giving me the error "
    ORA-14196: Specified index cannot be used to enforce the constraint."

    Please help.
    Sneha
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    Your table is partitioned?

    Code (SQL):
    DROP TABLE test_uc purge;
    CREATE TABLE test_uc
    (
        id INT
    );

    INSERT INTO test_uc SELECT MOD(level,3) FROM dual CONNECT BY level <=10;
    commit;

    CREATE INDEX test_uc_i ON test_uc(id);

    ALTER TABLE  test_uc  ADD CONSTRAINT test_uc UNIQUE(id) USING INDEX test_uc_i novalidate ;

    INSERT INTO test_uc VALUES(0);

    SELECT * FROM user_indexes i WHERE i.TABLE_NAME = 'TEST_UC';
    P.S.
    Best of solution this is reorganization of your data and create unique constraint
     
    Last edited: Apr 19, 2016
  3. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    Hi,

    Thanks for the solution.
    But I can neither do any changes in the existing data, nor drop the table..
    If I use the normal index for my unique constraint, it is working but, since i need to put condition for that uniqueness, I need to make function based index only.
    Which is not allowing me to use for the unique constraint.

    Just for the example
    CREATE TABLE EMP_TEST
    (EMP_ID NUMBER,
    EMP_JOB NUMBER,
    EMP_DEPT NUMBER)

    INSERT INTO EMP_TEST VALUES(1,1,10)
    INSERT INTO EMP_TEST VALUES(1,1,10)
    INSERT INTO EMP_TEST VALUES(1,2,20)
    INSERT INTO EMP_TEST VALUES(1,2,20)

    now after this data for further i want to put control so that no one can put duplicate job for same employee for only dept 10.,

    CREATE INDEX INDX ON EMP_TEST(CASE WHEN EMP_DEPT=10 THEN EMP_JOB ELSE NULL END)

    ALTER TABLE EMP_TEST ADD CONSTRAINT UNQ_JOB_4_DEPT10 UNIQUE(EMP_ID) USING INDEX INDX ENABLE NOVALIDATE

    which is giving me "ORA-14196: Specified index cannot be used to enforce the constraint."
     
    Last edited: Apr 19, 2016
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    What do you want to make?
     
    Last edited: Apr 19, 2016
  5. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    In simple words i want to create an unique constraint with some conditions and novalidate clause.
     
  6. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    what oracle version ?

    this example only for testing.... ))

    Code (SQL):
    DROP TABLE EMP_TEST purge;
    DROP FUNCTION fn_idx;

    CREATE OR REPLACE FUNCTION fn_idx (emp_id NUMBER,emp_job NUMBER,emp_dept NUMBER)
    RETURN varchar2 deterministic
    AS
    BEGIN
        RETURN substr(emp_id||emp_job||emp_dept||CASE WHEN emp_dept != 10 THEN to_char(sysdate,'SSSSSSS') END,1,100);
    END;
    /

    CREATE TABLE EMP_TEST
    (EMP_ID NUMBER,
    EMP_JOB NUMBER,
    EMP_DEPT NUMBER,
    is_unq_job_part_dept AS
    (
        fn_idx(emp_id,emp_job,emp_dept)
    )
    );

    CREATE INDEX emp_test_i ON emp_test(is_unq_job_part_dept);

    INSERT INTO EMP_TEST(emp_id,emp_job,emp_dept) VALUES(1,1,10);
    INSERT INTO EMP_TEST(emp_id,emp_job,emp_dept) VALUES(1,1,10);
    INSERT INTO EMP_TEST(emp_id,emp_job,emp_dept) VALUES(1,1,20);
    INSERT INTO EMP_TEST(emp_id,emp_job,emp_dept) VALUES(1,2,10);
    INSERT INTO EMP_TEST(emp_id,emp_job,emp_dept) VALUES(1,2,20);
    INSERT INTO EMP_TEST(emp_id,emp_job,emp_dept) VALUES(1,2,20);
    commit;

    ALTER TABLE emp_test ADD CONSTRAINT emp_test_uc UNIQUE(is_unq_job_part_dept) USING INDEX emp_test_i novalidate;

    SELECT * FROM emp_test;

    INSERT INTO EMP_TEST(emp_id,emp_job,emp_dept) VALUES(1,1,10);

     
     
    Last edited: Apr 20, 2016
  7. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
     
  8. sneha.bharti

    sneha.bharti Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Lagos
    It worked...Thanks a lot...
    Just have one query, what if I don't want to add any extra column in existing table...Is there any way i could avoid that..

    Thanks again
     
  9. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have already tried that and failed, so no, it's not possible. Your attempt only indexed part of the table data so the index could not be used to enforce a unique constraint.