Can we add a new column having multiple null values to already existing unique keys in the table?

There is a table having unique key on a combination of five columns. A new column has been added in the table, hence older records is all null for the newly added columns. I need to add this new column as part of the already existing unique key. Is it possible? if yes, please provide syntax too.

Best Answers

  • Jim_KnicelyJim_Knicely - Select Field - Administrator
    edited December 2018 Answer ✓

    Correct.

    Example:

    dbadmin=> CREATE TABLE test (c1 INT, c2 INT, c3 INT, c4 INT, c5 INT);
    CREATE TABLE
    
    dbadmin=> ALTER TABLE test ADD CONSTRAINT test_uk UNIQUE(c1, c2, c3, c4) ENABLED;
    ALTER TABLE
    
    dbadmin=> INSERT INTO test SELECT 1, 2, 3, 4, NULL;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> ALTER TABLE test DROP CONSTRAINT test_uk;
    ALTER TABLE
    
    dbadmin=> ALTER TABLE test ADD CONSTRAINT test_uk UNIQUE(c1, c2, c3, c4, c5) ENABLED;
    ALTER TABLE
    
    dbadmin=> INSERT INTO test SELECT 1, 2, 3, 4, NULL;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> SELECT * FROM test;
     c1 | c2 | c3 | c4 | c5
    ----+----+----+----+----
      1 |  2 |  3 |  4 |
      1 |  2 |  3 |  4 |
    (2 rows)
    
    dbadmin=> SELECT analyze_constraints('test');
     Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values
    -------------+------------+--------------+-----------------+-----------------+---------------
    (0 rows)
    
    dbadmin=> INSERT INTO test SELECT 1, 2, 3, 4, 5;
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> INSERT INTO test SELECT 1, 2, 3, 4, 5;
    ERROR 6745:  Duplicate key values: 'c1=1,c2=2,c3=3,c4=4,c5=5' -- violates constraint 'public.test.test_uk'
    

Answers

  • Thanks for the great explanation @Jim_Knicely ... this shows addition of a new column into unique keys, allows duplicates with nulls ( which was restricted previously)... this is strange, and looks like does not solve the purpose of the uniqueness :-(

  • @Jim_Knicely : actually, the new column which has been added in the database, does not store values for old records, and the remaining columns in the unique keys have rejected duplicate records. So, if the new column is included in the unique key, the old records would have nulls, and even if the other columns are duplicates, for a new record, the new record woould be allowed since the latest columns contains null there. For eg, c1,c2,c3,c4,c5 have values 1,2,3,4,null .. here c5 is the new column added in the unique key. again if the same row is inserted into the DB i.e. 1,2,3,4,null, this record would be allowed as per the example above. Is this correct?

  • Thanks @Jim_Knicely for such a great clarification :-) :smile:

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file