Column-constraint

Adds a constraint to a column's metadata.

Adds a constraint to a column's metadata.

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 ] ]
}

Arguments

You can specify enforcement of several constraints by qualifying them with the keywords ENABLED or DISABLED. See Enforcing Constraints below.

{ AUTO_INCREMENT | IDENTITY } [ (args) ]
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. You cannot set this constraint in a temporary table.

AUTO_INCREMENT and IDENTITY are synonyms.

The following arguments can be added:

( { cache-size | start }, increment, [ cache-size ] )

  • start: First value to set for this column (default 1).

  • increment: How much to change the value for each new row insertion, a positive or negative value (default 1).

  • cache-size: How many unique values each node caches per session, or 0 or 1 to disable (default 250,000).

For more information about these arguments, see IDENTITY sequences.

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)
Sets a Boolean condition to check.
DEFAULT default-expr
Sets this column's default value. Vertica evaluates the 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 using-expr
Sets column values from an expression. Vertica evaluates the expression and refreshes column values only when REFRESH_COLUMNS is invoked. For details about valid expressions, see Defining column values.
DEFAULT USING expression
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
Whether the column allows 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: Requires the column to 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.

The default is NULL for all columns except primary key columns, which Vertica always sets to NOT NULL.

For an external table, data violating a NOT NULL constraint can produce unexpected results when the table is queried. Use NOT NULL for an external table column only if you are sure that the data does not contain nulls.

PRIMARY KEY
Identifies this column as the table's primary key.
REFERENCES table [ ( column ) ]
Identifies this column as a foreign key to a column in another table. If you omit the column, Vertica uses the table's primary key.
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 constraints configuration parameter:

  • EnableNewPrimaryKeysByDefault

  • EnableNewUniqueKeysByDefault

  • EnableNewCheckConstraintsByDefault

See also