5 Replies Latest reply: Apr 2, 2014 2:05 PM by Brian Tkatch RSS

    Two COLUMNs need to be UNIQUE if the second isn't NULL.

    Brian Tkatch

      Two COLUMNs need to be UNIQUE if the second isn't NULL. If the second is NULL, the first does not need to be UNIQUE.

       

      CREATE TABLE Moo
      (
      Prima        INT PRIMARY KEY,
      Secunda    INT NOT NULL,
      Tertia        VARCHAR2(1)
      );
      
      CREATE UNIQUE INDEX Cow ON Moo(Secunda, NVL(Tertia, TO_CHAR(Prima)));
      
      INSERT INTO Moo(Prima, Secunda, Tertia)
      SELECT 1, 1, NULL FROM Dual UNION ALL
      SELECT 2, 1, NULL FROM Dual;
      
      DROP TABLE Moo;
      
      

       

      Is there another way to do it?

       

      Added "UNIQUE"

        • 1. Re: Two COLUMNs need to be UNIQUE if the second isn't NULL.
          Frank Kulash

          Hi,

           

           

           

          Brian Tkatch wrote:

           

          Two COLUMNs need to be UNIQUE if the second isn't NULL. If the second is NULL, the first does not need to be UNIQUE.

           

          1. CREATE TABLE Moo  
          2. (  
          3. Prima        INT PRIMARY KEY,  
          4. Secunda    INT NOT NULL,  
          5. Tertia        VARCHAR2(1)  
          6. );  
          7. CREATE INDEX Cow ON Moo(Secunda, NVL(Tertia, TO_CHAR(Prima)));  
          8. INSERT INTO Moo(Prima, Secunda, Tertia)  
          9. SELECT 1, 1, NULL FROM Dual UNION ALL 
          10. SELECT 2, 1, NULL FROM Dual;  
          11. DROP TABLE Moo; 

           

          Is there another way to do it?

          Sorry, it's unclear what you want to do. It would help if you posted a few examples of INSERT statements, some of which should work, and some of which should fail because they violate the uniqueness requirements.  Identify which statements should work, and which should fail.

           

          The index you created won't enforce uniqueness; only a UNIQUE index or constraint will do that.

           

          Perhaps a function-based index will do what you want.  For example:

          CREATE UNIQUE INDEX moo_unique

          ON moo ( NVL2 (secunda, prima,  NULL)

                 , NVL2 (secunda, tertia, NULL)

                 );

          The effect of the index above is to have a unique index on (prima, tertia) that only applies to rows where secunda is not NULL.

          • 2. Re: Two COLUMNs need to be UNIQUE if the second isn't NULL.
            Brian Tkatch

            Frank, thanx. I went through a few edits and ended up mistakenly dropping the UNIQUE keyword. I added it back to the script.

             

            A case where a record will fail, is to use a CONSTRAINT instead of an INDEX:

             

            CREATE TABLE Moo
            (
            Prima        INT PRIMARY KEY,
            Secunda    INT NOT NULL,
            Tertia        VARCHAR2(1),
            UNIQUE(Secunda, Tertia)
            );
            
            INSERT INTO Moo(Prima, Secunda, Tertia)
            SELECT 1, 1, NULL FROM Dual UNION ALL
            SELECT 2, 1, NULL FROM Dual;
            
            DROP TABLE Moo;
            
            
            INSERT INTO Moo(Prima, Secunda, Tertia)
            *
            ERROR at line 1:
            ORA-00001: unique constraint (BTKATCH2.SYS_C0086823) violate
            
            • 3. Re: Two COLUMNs need to be UNIQUE if the second isn't NULL.
              Frank Kulash

              Hi,

               

               

              Brian Tkatch wrote:

               

              Frank, thanx. I went through a few edits and ended up mistakenly dropping the UNIQUE keyword. I added it back to the script.

               

              A case where a record will fail, is to use a CONSTRAINT instead of an INDEX:

               

              1. CREATE TABLE Moo   
              2. (   
              3. Prima        INT PRIMARY KEY,   
              4. Secunda    INT NOT NULL,   
              5. Tertia        VARCHAR2(1),  
              6. UNIQUE(Secunda, Tertia)  
              7. );   
              8. INSERT INTO Moo(Prima, Secunda, Tertia)   
              9. SELECT 1, 1, NULL FROM Dual UNION ALL   
              10. SELECT 2, 1, NULL FROM Dual;   
              11. DROP TABLE Moo;  

              Sorry, I didn't read this carefully at first.

              So, prima is the primary key, but it doesn;'t really play any role in this problem.

              Secunda is declared as NOT NULL, so there's no point in talking about cases where secunda is NULL.

               

              If the unique constraint that you posted does what you want, then use it.  I can't see any better way to get the same results.

              In general, don't (explicitly) use an index when you can use a constraint.  And don't use a trigger when you can use anything else.

              • 4. Re: Two COLUMNs need to be UNIQUE if the second isn't NULL.
                Jonathan Lewis

                I had a bit of trouble matching your description to your example, but if you want to enforce uniqueness on (secunda, tertia) only for rows where tertia is not null then the following should work:

                 

                create unique index xxx on moo(

                  case when tertia is not null then secunda end,

                  tertia

                );

                 

                Regards

                Jonathan Lewis

                 

                P.S.  When doing a quick test of how best to define this index, I found what seems to be a bug in 11.2.0.4 (maybe other versions) with the expression:

                case tertia when null then cast(null as int) else secunda end

                 

                Update: now blogged at: Easy – Oops. | Oracle Scratchpad

                • 5. Re: Two COLUMNs need to be UNIQUE if the second isn't NULL.
                  Brian Tkatch

                  > if you want to enforce uniqueness on (secunda, tertia) only for rows where tertia is not null

                   

                  Yes, exactly. I see that being concise was not better in this case.

                   

                  And that is a better way to do it as it does not require the PK. Took a second look to realize how it worked, but its perfect. Thanx!