Column-Constraint
Adds a constraint to a column's metadata. See Adding 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 | REFERENCES table [( column )] } [ ENABLED | DISABLED ] ] ...[ UNIQUE [ ENABLED | DISABLED ] ] }
[ CONSTRAINT constraint‑name ] { ...| CHECK (expression) [ ENABLED | DISABLED ] ...[ DEFAULT default‑expr ] ...[ NULL | NOT NULL ] ...[ { PRIMARY KEY | REFERENCES table [( column )] } [ ENABLED | DISABLED ] ] ...[ SET USING using‑expr ] ...| UNIQUE [ ENABLED | DISABLED ] }
Parameters
Note: A number of parameters can be qualified with the keyword ENABLED
or DISABLED
. For details, 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.
|
CONSTRAINT constraint‑name
|
Assigns a name to the constraint. 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
|
Defines the 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 Enforcing Primary Key, Unique Key, and Check Constraints Automatically.