1 Reply Latest reply on Apr 20, 2020 10:50 PM by Glen Conway

    Unique constraint with sql developer dialog box

    4130441

      Hi!

       

      Is it possible to create a unique constraint with conditions in the dialog boxes, e.g the unique constraints pane of the sql developer?

      More precisely, I need a unique constraint that checks whether the combination of type and person is unique but only if type = 1.

       

      I really appreciate your help!

      Thanks Barbara

        • 1. Re: Unique constraint with sql developer dialog box
          Glen Conway

          This looks like one of those AskTom questions.  If we follow the advice in https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1249800833250 ,

          you need to create a function-based unique index on a function taking type and person as parameters, that returns person only if type is 1, and null otherwise.

           

          Assuming type is a number and person is a varchar2, first create the function (using this DDL in a worksheet)

          create or replace function person_of_type_one( p_person in varchar2, p_type in number ) return varchar2
            DETERMINISTIC
            as
              begin
                if ( p_type = 1 )
                  then
                    return p_person;
                  else
                      return NULL;
                end if;
            end;
          

          then create the index (in a worksheet)

          create unique index person_of_type_one_idx on <your_person_table>(person_of_type_one(person, type) )

           

          I did not test this, just copied the AskTom case and modified it slightly.  It seems that creating a virtual column in your table (Edit Table > Columns > Data Type > Virtual > Virtual Expression), then basing a unique index on it should also work, but I am not positive.

           

          Cheers

          1 person found this helpful