Vertica Analytics Platform Version 9.2.x Documentation

NOT NULL Constraints

A NOT NULL> constraint specifies that a column cannot contain a null value. All table updates must specify values in columns with this constraint. You can set a NOT NULL constraint on columns when you create a table, or set the constraint on an existing table with ALTER TABLE.

The following CREATE TABLE statement defines three columns as NOT NULL. You cannot store any NULL values in those columns.

=> CREATE TABLE inventory ( date_key INTEGER NOT NULL, 
                            product_key INTEGER NOT NULL, 
                            warehouse_key INTEGER NOT NULL, ... );

The following ALTER TABLE statement defines column sku_number in table product_dimensions as NOT NULL:

=> ALTER TABLE public.product_dimension ALTER COLUMN sku_number SET NOT NULL;
ALTER TABLE

Enforcing NOT NULL Constraints

You cannot enable enforcement of a NOT NULL constraint. You must use ANALYZE_CONSTRAINTS to determine whether column data contains null values, and then manually fix any constraint violations that the function finds.

NOT NULL and Primary Keys

When you define a primary key, Vertica automatically sets the primary key columns to NOT NULL. If you drop a primary key constraint, the columns that comprised it remain set to NOT NULL. This constraint can only be removed explicitly, through ALTER TABLE…ALTER COLUMN.