table-constraint

Adds a join constraint or a constraint describing a functional dependency to the metadata of a table. See Adding Constraints in the Database Administrator's Guide.

Syntax

[ CONSTRAINT constraint_name ]

{ PRIMARY KEY ( column [ , ... ] )

| FOREIGN KEY ( column [ , ... ] )

REFERENCES table [ ( column [ , ... ] ) ]

| CORRELATION ( column1 ) DETERMINES ( column2 )

}

Semantics

CONSTRAINT constraint-name	optionally assigns a name to the constraint. Vertica recommends that you name all constraints.
PRIMARY KEY ( column [ , ... ] )	adds a referential integrity constraint defining one or more NOT NULL numeric columns 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.
FOREIGN KEY ( column [ , ... ] )	adds a referential integrity constraint defining one or more NOT NULL numeric columns as a foreign 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 table [ ( column [ , ... ] )	specifies the table to which the `FOREIGN KEY` constraint applies. If column is omitted, the default is the primary key of table.
CORRELATION	describes a functional dependency. Given a tuple and the set of values in column1, one can determine the corresponding value of column2. A functional dependency is a relationship between two sets of column values in a table where one column value can determine the other. It represents knowledge of the data that cannot otherwise be expressed in the logical schema. In Vertica functional dependencies are expressed as CORRELATION constraints and are used by the Database Designer to produce more efficient physical schema designs.

Notes

Use the ALTER TABLE command to add a table constraint. The CREATE TABLE statement does not allow table constraints.
You must define primary key and foreign key constraints in all tables that participate in inner joins. See Adding Join Constraints.

Examples

CORRELATION (Product_Description) DETERMINES (Category_Description)

The Retail Sales Example Database described in the Quick Start contains a table Product_Dimension in which products have descriptions and categories. For example, the description "Seafood Product 1" exists only in the "Seafood" category. You can define several similar correlations between columns in the Product Dimension table.

Column Name	Data Type	Example
Product_Key	integer	1
Product_Description	varchar(128)	Seafood Product 1
SKU_Number	char(32)	SKU-#1
Category_Description	char(32)	Food
Department_Description	char(32)	Seafood
Package_Type_Description	char(32)	Box
Package_Size	char(32)	18 ox
Fat_Content	integer	89
Diet_Type	char(32)	South Beach
Weight	integer	50
Weight_Units_of_Measure	char(32)	gram
Shelf_Width	integer	2
Shelf_Height	integer	4
Shelf_Depth	integer	4