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:

• CONSTRAINT_COLUMNS
• TABLE_CONSTRAINTS
• PRIMARY_KEYS

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