Naming Constraints
The following constraints must be named.
PRIMARY KEY
REFERENCES
(foreign key)CHECK
UNIQUE
You name these constraints when you define them. If you omit assigning a name, Vertica automatically assigns one.
User-Assigned Constraint Names
You assign names to constraints when you define them with CREATE TABLE
or ALTER TABLE…ADD CONSTRAINT
. For example, the following CREATE TABLE
statement names primary key and check constraints pk
and date_c
, respectively:
=> CREATE TABLE public.store_orders_2016 ( order_no int CONSTRAINT pk PRIMARY KEY, product_key int, product_version int, order_date timestamp NOT NULL, shipper varchar(20), ship_date date, CONSTRAINT date_c CHECK (date_part('year', order_date)::int = 2016) ) PARTITION BY ((date_part('year', order_date))::int); CREATE TABLE
The following ALTER TABLE
statement adds foreign key constraint fk
:
=> ALTER TABLE public.store_orders_2016 ADD CONSTRAINT fk
FOREIGN KEY (product_key, product_version)
REFERENCES public.product_dimension (product_key, product_version);
Auto-assigned Constraint Names
Naming a constraint is optional. If you omit assigning a name to a constraint, Vertica assigns its own name using the following convention:
C_constraint-type[_integer]
For example, the following table defines two columns a
and b
and constrains them to contain unique values:
=> CREATE TABLE t1 (a int UNIQUE, b int UNIQUE ); CREATE TABLE
When you export the table's DDL with EXPORT_TABLES
, the function output shows that Vertica assigned constraint names C_UNIQUE
and C_UNIQUE_1
to columns a
and b
, respectively:
=> SELECT EXPORT_TABLES('','t1'); CREATE TABLE public.t1 ( a int, b int, CONSTRAINT C_UNIQUE UNIQUE (a) DISABLED, CONSTRAINT C_UNIQUE_1 UNIQUE (b) DISABLED ); (1 row)
Viewing Constraint Names
You can view the names of table constraints by exporting the table's DDL with EXPORT_TABLES
, as shown earlier. You can also query the following system tables:
For example, the following query gets the names of all primary and foreign key constraints in schema online_sales
:
=> SELECT table_name, constraint_name, column_name, constraint_type FROM constraint_columns WHERE constraint_type in ('p','f') AND table_schema='online_sales' ORDER BY table_name, constraint_type, constraint_name; table_name | constraint_name | column_name | constraint_type -----------------------+---------------------------+-----------------+----------------- call_center_dimension | C_PRIMARY | call_center_key | p online_page_dimension | C_PRIMARY | online_page_key | p online_sales_fact | fk_online_sales_cc | call_center_key | f online_sales_fact | fk_online_sales_customer | customer_key | f online_sales_fact | fk_online_sales_op | online_page_key | f online_sales_fact | fk_online_sales_product | product_version | f online_sales_fact | fk_online_sales_product | product_key | f online_sales_fact | fk_online_sales_promotion | promotion_key | f online_sales_fact | fk_online_sales_saledate | sale_date_key | f online_sales_fact | fk_online_sales_shipdate | ship_date_key | f online_sales_fact | fk_online_sales_shipping | shipping_key | f online_sales_fact | fk_online_sales_warehouse | warehouse_key | f (12 rows)
Using Constraint Names
You must reference a constraint name in order to perform the following tasks:
- Enable or disable constraint enforcement.
- Drop a constraint.
For example, the following ALTER TABLE
statement enables enforcement of constraint pk
in table store_orders_2016
:
=> ALTER TABLE public.store_orders_2016 ALTER CONSTRAINT pk ENABLED; ALTER TABLE
The following statement drops another constraint in the same table:
=> ALTER TABLE public.store_orders_2016 DROP CONSTRAINT date_c; ALTER TABLE