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

Arti21

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.

Jim_Knicely

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

Jim_Knicely

@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

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'

Arti21

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

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

Arti21

Thanks @Jim_Knicely for such a great clarification :-)