Truly Unique Constraints

[Deleted User]

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: