Primary key constraints

A primary key comprises one or multiple columns of primitive types, whose values can uniquely identify table rows. A table can specify only one primary key. You identify a table's primary key when you create the table, or in an existing table with ALTER TABLE. You cannot designate a column with a collection type as a key.

For example, the following CREATE TABLE statement defines the order_no column as the primary key of the store_orders table:

=> CREATE TABLE public.store_orders(
    order_no int PRIMARY KEY,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date,
    product_key int,
    product_version int
)
PARTITION BY ((date_part('year', order_date))::int);
CREATE TABLE

Multi-column primary keys

A primary key can comprise multiple columns. In this case, the CREATE TABLE statement must specify the constraint after all columns are defined, as follows:

=> CREATE TABLE public.product_dimension(
    product_key int,
    product_version int,
    product_description varchar(128),
    sku_number char(32) UNIQUE,
    category_description char(32),
    CONSTRAINT pk PRIMARY KEY (product_key, product_version) ENABLED
);
CREATE TABLE

Alternatively, you can specify the table's primary key with a separate ALTER TABLE...ADD CONSTRAINT statement, as follows:

=> ALTER TABLE product_dimension ADD CONSTRAINT pk PRIMARY KEY (product_key, product_version) ENABLED;
ALTER TABLE

Enforcing primary keys

You can prevent loading duplicate values into primary keys by enforcing the primary key constraint. Doing so allows you to join tables on their primary and foreign keys. When a query joins a dimension table to a fact table, each primary key in the dimension table must uniquely match each foreign key value in the fact table. Otherwise, attempts to join these tables return a key enforcement error.

You enforce primary key constraints globally with configuration parameter EnableNewPrimaryKeysByDefault. You can also enforce primary key constraints for specific tables by qualifying the constraint with the keyword ENABLED. In both cases, Vertica checks key values as they are loaded into tables, and returns errors on any constraint violations. Alternatively, use ANALYZE_CONSTRAINTS to validate primary keys after updating table contents. For details, see Constraint enforcement.

Setting NOT NULL on primary keys

When you define a primary key , Vertica automatically sets the primary key columns to NOT NULL. For example, when you create the table product_dimension as shown earlier, Vertica sets primary key columns product_key and product_version to NOT NULL, and stores them in the catalog accordingly:

> SELECT EXPORT_TABLES('','product_dimension');
...
CREATE TABLE public.product_dimension
(
    product_key int NOT NULL,
    product_version int NOT NULL,
    product_description varchar(128),
    sku_number char(32),
    category_description char(32),
    CONSTRAINT C_UNIQUE UNIQUE (sku_number) DISABLED,
    CONSTRAINT pk PRIMARY KEY (product_key, product_version) ENABLED
);

(1 row)

If you specify a primary key for an existing table with ALTER TABLE, Vertica notifies you that it set the primary key columns to NOT NULL:

WARNING 2623: Column "column-name" definition changed to NOT NULL