column-constraint

column-constraint

Adds a referential integrity constraint to the metadata of a column. See Adding Join Constraints in the Database Administrator's Guide.

Syntax

[ CONSTRAINT constraint-name ]

{ [ NOT ] NULL

| PRIMARY KEY

| REFERENCES table-name [ ( column-name ) ]

[ DEFAULT constant ]

}

Semantics

CONSTRAINT constraint-name	optionally assigns a name to the constraint. Vertica recommends that you name all constraints.
NULL	(default) specifies that the column is allowed to contain null values.
NOT NULL	specifies that the column must receive a value during INSERT and UPDATE operations. If no DEFAULT value is specified and no value is provided, the INSERT or UPDATE statement returns an error because no default value exists.
PRIMARY KEY	adds a referential integrity constraint defining the column as the primary key. Referential integrity in Vertica consists of a set of constraints (logical schema objects) that define primary key and foreign key columns. In a star schema or snowflake schema: Each dimension table must have a PRIMARY KEY constraint. The fact table must contain columns that can be used to join the fact table to dimension tables. Fact table join columns must have FOREIGN KEY constraints in order to participate in pre-join projections. Outer join queries produce expected results only when the fact table join column used in the query does not have a FOREIGN KEY constraint.
REFERENCES	adds a referential integrity constraint defining the column as a foreign key. If column is omitted, the default is the primary key of table.
table-name	specifies the table to which the REFERENCES constraint applies.
column-name	specifies the column to which the REFERENCES constraint applies. If column is omitted, the default is the primary key of table-name.
DEFAULT constant	specifies a constant to be used by default in any INSERT operation that does not specify a value for the column. The data type of the constant must match the data type of the column. If no default value is specified, the default is null.

Notes

You must define primary key and foreign key constraints in all tables that participate in joins. See Adding Constraints.
You must specify NOT NULL constraints on columns that will be given PRIMARY and REFERENCES constraints.
Vertica does not support expressions in the DEFAULT clause.