Forum Stats

  • 3,723,261 Users
  • 2,244,520 Discussions
  • 7,850,383 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

Brian Tkatch
Brian Tkatch Member Posts: 337
edited April 2014 in SQL & PL/SQL

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"

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,560 Gold Crown
    edited April 2014 Accepted Answer

    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

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,195 Red Diamond
    edited April 2014

    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; 
    CREATE TABLE Moo
    (
     Prima        INT PRIMARY KEY,
     Secunda    INT NOT NULL,
     Tertia        VARCHAR2(1)
    );
    CREATE 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?
     

    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.

    Frank Kulash
  • Brian Tkatch
    Brian Tkatch Member Posts: 337
    edited April 2014

    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
    
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,195 Red Diamond

    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;  
    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; 

    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.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,560 Gold Crown
    edited April 2014 Accepted Answer

    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

  • Brian Tkatch
    Brian Tkatch Member Posts: 337

    > 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!

This discussion has been closed.