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

Sure.

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, c5) ENABLED;
ALTER TABLE

dbadmin=> INSERT INTO test SELECT 1, 2, 3, 4, 5;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO test SELECT 6, 7, 8, 9, 10;
 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'

dbadmin=> ALTER TABLE test ADD COLUMN c6 INT;
ALTER TABLE

dbadmin=> ALTER TABLE test DROP CONSTRAINT test_uk;
ALTER TABLE

dbadmin=> ALTER TABLE test ADD CONSTRAINT test_uk UNIQUE(c1, c2, c3, c4, c5, c6) ENABLED;
ALTER TABLE

dbadmin=> INSERT INTO test (c1, c2, c3, c4, c5) SELECT 1, 2, 3, 4, 5;
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM test;
 c1 | c2 | c3 | c4 | c5 | c6
----+----+----+----+----+----
  1 |  2 |  3 |  4 |  5 |
  6 |  7 |  8 |  9 | 10 |
  1 |  2 |  3 |  4 |  5 |
(3 rows)

But that appears to allow DUPS!

Check out this post on how to only allow one NULL in a unique constraint:

https://forum.vertica.com/discussion/239383/truly-unique-constraints

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 :-(

@Arti21 - Not sure what you mean. Multiple NULL values in Vertica will not violate a UNIQUE constraint. That makes sense to me. I know other databases like SQL Server (Optionally) do not allow that, but think about it. NULL means unknown.

Example:

dbadmin=> CREATE TABLE a (c INT);
CREATE TABLE

dbadmin=> ALTER TABLE a ADD CONSTRAINT a_uk UNIQUE(c) ENABLED;
ALTER TABLE

dbadmin=> INSERT INTO a SELECT NULL;
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO a SELECT NULL;
 OUTPUT
--------
      1
(1 row)

In a true warehouse, I would never store NULL.I would use "UNKNOWN" for example.

@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?

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'

Thanks @Jim_Knicely for such a great clarification :-)