Column-constraint

Adds a constraint to a column's metadata. For details, see Constraints.

Syntax

[ { AUTO_INCREMENT | IDENTITY } [ (args) ] ]
[ CONSTRAINT constraint-name ] {
   [ CHECK (expression) [ ENABLED | DISABLED ] ]
   [ [ DEFAULT expression ] [ SET USING expression } | DEFAULT USING expression ]
   [ NULL | NOT NULL ]
   [ { PRIMARY KEY [ ENABLED | DISABLED ] REFERENCES table [( column )] } ]
   [ UNIQUE [ ENABLED | DISABLED ] ]
}

Parameters

AUTO_INCREMENT | IDENTITY

Creates a table column whose values are automatically generated by and managed by the database. You cannot change or load values in this column. You can set this constraint on only one table column.

AUTO_INCREMENT and IDENTITY are synonyms. For details on this constraint and optional arguments, see IDENTITY sequences.

These options are invalid for temporary tables.

CONSTRAINT constraint-name

Assigns a name to the constraint, valid for the following constraints:

• PRIMARY KEY

• REFERENCES (foreign key)

• CHECK

• UNIQUE

If you omit assigning a name to these constraints, Vertica assigns its own name. For details, see Naming constraints.

Vertica recommends that you name all constraints.

CHECK (expression)

Adds check condition expression, which returns a Boolean value.

DEFAULT

Specifies this column's default value:

DEFAULT default-expr

Vertica evaluates the DEFAULT expression and sets the column on load operations, if the operation omits a value for the column. For details about valid expressions, see Defining column values.

SET USING

Specifies to set values in this column from the specified expression:

SET USING using-expr 

Vertica evaluates the SET USING expression and refreshes column values only when the function REFRESH_COLUMNS is invoked. For details about valid expressions, see Defining column values.

DEFAULT USING

Defines the column with DEFAULT and SET USING constraints, specifying the same expression for both. DEFAULT USING columns support the same expressions as SET USING columns, and are subject to the same restrictions.

NULL | NOT NULL

Specifies whether the column can contain null values:

• NULL: Allows null values in the column. If you set this constraint on a primary key column, Vertica ignores it and sets it to NOT NULL.

• NOT NULL: Specifies that the column must be set to a value during insert and update operations. If the column has no default value and no value is provided, INSERT or UPDATE returns an error.

If you omit this constraint, the default is NULL for all columns except primary key columns, which Vertica always sets to NOT NULL.

External tables: If you specify NOT NULL and the column contains null values, queries are liable to return errors or generate unexpected behavior. Specify NOT NULL for an external table column only if you are sure that the column does not contain nulls.

PRIMARY KEY

Identifies this column as the table's primary key.

REFERENCES

Identifies this column as a foreign key:

REFERENCES table [column]

where column is the primary key in table. If you omit column, Vertica references the primary key in table.

UNIQUE

Requires column data to be unique with respect to all table rows.

Privileges

Table owner or user WITH GRANT OPTION is grantor.

• REFERENCES privilege on table to create foreign key constraints that reference this table

• USAGE privilege on schema that contains the table

Enforcing constraints

The following constraints can be qualified with the keyword ENABLED or DISABLED:

• PRIMARY KEY

• UNIQUE

• CHECK

If you omit ENABLED or DISABLED, Vertica determines whether to enable the constraint automatically by checking the appropriate configuration parameter:

• EnableNewPrimaryKeysByDefault

• EnableNewUniqueKeysByDefault

• EnableNewCheckConstraintsByDefault

For details, see Constraint enforcement.