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.
Consider using sequences for primary key columns to guarantee uniqueness, and avoid the resource overhead that primary key constraints can incur.
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
If you drop a primary key constraint, the columns that comprised it remain set to NOT NULL
. This constraint can only be removed explicitly, through ALTER TABLE…ALTER COLUMN
.