Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    21

    Unanswered: Unique index on 2 columns nullables

    Hi All,

    I find to be able to have multiple NULL entries in the following constraint:
    Code:
    CREATE UNIQUE INDEX my_uidx ON my_table(my_col1,my_col2) WHERE ??? IS NOT NULL;
    But is not possible to check multiple colums in "WHERE".

    I using SQL Server 2012 Express

    Do you know another solution ?

    Thanks.

    Nassa.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE nassarane (
       nassarane_1  INT     NULL
    ,  nassarane_2  INT     NULL
       )
    
    CREATE INDEX nassarane_UF01
       ON nassarane
       (nassarane_1, nassarane_2)
       WHERE (nassarane_1 IS NOT NULL
          AND nassarane_2 IS NOT NULL)
    
    INSERT INTO nassarane (nassarane_1, nassarane_2) VALUES (NULL, NULL)
    INSERT INTO nassarane (nassarane_1, nassarane_2) VALUES (   1, NULL)
    INSERT INTO nassarane (nassarane_1, nassarane_2) VALUES (NULL,    1)
    INSERT INTO nassarane (nassarane_1, nassarane_2) VALUES (   1,    1)
    
    --  Intuitively, this ought to fail but it works!
    INSERT INTO nassarane (nassarane_1, nassarane_2) VALUES (NULL, NULL)
    
    SELECT *
       FROM nassarane;
    Note that this is syntactically correct, but it won't do what you want.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Cool I will argue that this is okay

    CREATE TABLE Nassarane
    (nassarane_1 INTEGER,
    nassarane_2 INTEGER);

    CREATE INDEX Nassarane_UF01
    ON Nassarane (nassarane_1, nassarane_2)
    WHERE (nassarane_1 IS NOT NULL
    AND nassarane_2 IS NOT NULL);
    -- Intuitively, this ought to fail but it works!
    INSERT INTO nassarane (nassarane_1, nassarane_2) VALUES (NULL, NULL);
    I will argue that since indexing is not part of ANSI/ISO Standards, the only rule is that adding, dropping or altering an index declaration cannot change the outcome of any DDL or DML on the base table. Just the performance.

    The table declaration allows (NULL, NULL), so this is logical and expected.

    This is one reason that the old 1970's Sybase UPDATE.. FROM.. is a nightmare. Different index orderings get a different result!

    The WITH CHECK OPTION could be used to get all kinds of complex constraints, tho.

  4. #4
    Join Date
    Feb 2013
    Posts
    21
    Hi all,

    And thanks for you responses.
    I would like to put a UNIQUE constraint but also have the ability to add several times the my_col1 and my_col2 to null.
    It's possible in MySQL and Oracle, but note possible with DB2.
    With DB2 I used this : https://www.ibm.com/developerworks/c...exes26?lang=en

    For SQL Server it's possible with one key, with more I don't know...

    Thanks.

    Nassa.
    Last edited by nassarane; 04-22-13 at 06:46.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •