Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Truly Unique Constraints

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:


Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.