Vertica Quick Tip: A Truly Unique Constraint

Posted January 29, 2018 by Soniya Shah, Information Developer

This blog post was authored by Jim Knicely.

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!