Column-Constraint
Adds a constraint to a column's metadata. For details, see Constraints in the Administrator's Guide.
Syntax
[ { AUTO_INCREMENT | IDENTITY } [ (args) ] ] [ CONSTRAINT constraint‑name ] { ...[ CHECK (expression) [ ENABLED | DISABLED ] ] ...[ [ DEFAULT default‑expr ] [ SET USING using‑expr ] | DEFAULT USING expr ] ...[ NULL | NOT NULL ] ...[ { PRIMARY KEY [ ENABLED | DISABLED ] | REFERENCES table [( column )] } ] ...[ UNIQUE [ ENABLED | DISABLED ] ] }
Parameters
You can specify enforcement of several constraints by qualifying them with the keywords ENABLED
or DISABLED
. See Enforcing Constraints below.
AUTO_INCREMENT
|
Creates a table column whose values are automatically generated by the database, and cannot be changed. You can set this constraint on only one table column.
These options are invalid for temporary tables. |
CONSTRAINT constraint‑name
|
Assigns a name to the constraint, valid for the following constraints:
If you omit assigning a name to these constraints, Vertica assigns its own name. For details, see Naming Constraints in the Administrator's Guide. 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 |
SET USING
|
Specifies to set values in this column from the specified expression: SET USING using‑expr
Vertica evaluates the |
DEFAULT USING
|
Defines the column with |
NULL
|
Specifies whether the column can contain null values:
If you omit this constraint, the default is External tables: If you specify |
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.