According to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a UNIQUE constraint should disallow duplicate non-NULL values, but allow multiple NULL values.
A Unique Constraint in Vertica does just that!
Example:
dbadmin=> CREATE TABLE test (c1 INT);
CREATE TABLE
dbadmin=> ALTER TABLE test ADD CONSTRAINT test_uk UNIQUE (c1);
ALTER TABLE
dbadmin=> INSERT INTO test SELECT 1;
OUTPUT
--------
1
(1 row)
dbadmin=> INSERT INTO test SELECT 1;
ERROR 6745: Duplicate key values: 'c1=1' -- violates constraint 'public.test.test_uk'
dbadmin=> INSERT INTO test SELECT NULL;
OUTPUT
--------
1
(1 row)
dbadmin=> INSERT INTO test SELECT NULL;
OUTPUT
--------
1
(1 row)
But what if I do not want to allow more than one NULL value? Well, there’s a simple trick for that!Example:
dbadmin=> TRUNCATE TABLE test;
dbadmin=> ALTER TABLE test ADD COLUMN allow_only_1_null INT DEFAULT NVL2(c1, c1, -1);
ALTER TABLE
dbadmin=> ALTER TABLE test ADD CONSTRAINT test_uk2 UNIQUE (allow_only_1_null);
ALTER TABLE
dbadmin=> INSERT INTO test SELECT NULL;
OUTPUT
--------
1
(1 row)
dbadmin=> INSERT INTO test SELECT NULL;
ERROR 6745: Duplicate key values: 'allow_only_1_null=-1' -- violates constraint 'public.test.test_uk2'
Have Fun!
About the Author
Soniya Shah
Information Developer
Currently, a first year law student with a background in science and technology. Experienced technical writer, with specializations in software documentation, big data, blog development, and website development. I build user-centered content to communicate complex and technical information more easily.
I used to work for Vertica full time for about 3 years. I still work at Vertica part time while going to law school.
Update: Soniya is now doing her law internship, and no longer working at Vertica. Good luck, Soniya!
