Supported constraints

• 1: Primary key constraints
• 2: Foreign key constraints
• 3: Unique constraints
• 4: Check constraints
• 5: NOT NULL constraints

1 - 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

2 - Foreign key constraints

A foreign key joins a table to another table by referencing its primary key. A foreign key constraint specifies that the key can only contain values that are in the referenced primary key, and thus ensures the referential integrity of data that is joined on the two keys.

You can identify a table's foreign key when you create the table, or in an existing table with ALTER TABLE. For example, the following CREATE TABLE statement defines two foreign key constraints: fk_store_orders_store and fk_store_orders_vendor:

=> CREATE TABLE store.store_orders_fact(
    product_key int NOT NULL,
    product_version int NOT NULL,
    store_key int NOT NULL CONSTRAINT fk_store_orders_store REFERENCES store.store_dimension (store_key),
    vendor_key int NOT NULL CONSTRAINT fk_store_orders_vendor REFERENCES public.vendor_dimension (vendor_key),
    employee_key int NOT NULL,
    order_number int NOT NULL,
    date_ordered date,
    date_shipped date,
    expected_delivery_date date,
    date_delivered date,
    quantity_ordered int,
    quantity_delivered int,
    shipper_name varchar(32),
    unit_price int,
    shipping_cost int,
    total_order_cost int,
    quantity_in_stock int,
    reorder_level int,
    overstock_ceiling int
);

The following ALTER TABLE statement adds foreign key constraint fk_store_orders_employee to the same table:

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_employee
       FOREIGN KEY (employee_key) REFERENCES public.employee_dimension (employee_key);

The REFERENCES clause can omit the name of the referenced column if it is the same as the foreign key column name. For example, the following ALTER TABLE statement is equivalent to the one above:

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_employee
       FOREIGN KEY (employee_key) REFERENCES public.employee_dimension;

Multi-column foreign keys

If a foreign key refrences a primary key that contains multiple columns, the foreign key must contain the same number of columns. For example, the primary key for table public.product_dimension contains two columns, product_key and product_version. In this case, CREATE TABLE can define a foreign key constraint that references this primary key as follows:

=> CREATE TABLE store.store_orders_fact3(
    product_key int NOT NULL,
    product_version int NOT NULL,
    ...
   CONSTRAINT fk_store_orders_product
     FOREIGN KEY (product_key, product_version) REFERENCES public.product_dimension (product_key, product_version)
);

CREATE TABLE

CREATE TABLE can specify multi-column foreign keys only after all table columns are defined. You can also specify the table's foreign key with a separate ALTER TABLE...ADD CONSTRAINT statement:

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_product
     FOREIGN KEY (product_key, product_version) REFERENCES public.product_dimension (product_key, product_version);

In both examples, the constraint specifies the columns in the referenced table. If the referenced column names are the same as the foreign key column names, the REFERENCES clause can omit them. For example, the following ALTER TABLE statement is equivalent to the previous one:

=> ALTER TABLE store.store_orders_fact ADD CONSTRAINT fk_store_orders_product
     FOREIGN KEY (product_key, product_version) REFERENCES public.product_dimension;

NULL values in foreign key

A foreign key that whose columns omit NOT NULL can contain NULL values, even if the primary key contains no NULL values. Thus, you can insert rows into the table even if their foreign key is not yet known.

3 - Unique constraints

You can specify a unique constraint on a column so each value in that column is unique among all other values. You can define a unique constraint when you create a table, or you can add a unique constraint to an existing table with ALTER TABLE. You cannot use a uniqueness constraint on a column with a collection type.

For example, the following ALTER TABLE statement defines the sku_number column in the product_dimensions table as unique:

=> ALTER TABLE public.product_dimension ADD UNIQUE(sku_number);
WARNING 4887:  Table product_dimension has data. Queries using this table may give wrong results
if the data does not satisfy this constraint
HINT:  Use analyze_constraints() to check constraint violation on data

Enforcing unique constraints

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

For example, the previous example does not enforce the unique constraint in column sku_number. The following statement enables this constraint:

=> ALTER TABLE public.product_dimension ALTER CONSTRAINT C_UNIQUE ENABLED;
ALTER TABLE

Multi-column unique constraints

You can define a unique constraint that is comprised of multiple columns. The following CREATE TABLE statement specifies that the combined values of columns c1 and c2 in each row must be unique among all other rows:

CREATE TABLE dim1 (c1 INTEGER,
    c2 INTEGER,
    c3 INTEGER,
  UNIQUE (c1, c2) ENABLED
);

4 - Check constraints

A check constraint specifies a Boolean expression that evaluates a column's value on each row. If the expression resolves to false for a given row, the column value is regarded as violating the constraint.

For example, the following table specifies two named check constraints:

• IsYear2018 specifies to allow only 2018 dates in column order_date.

• Ship5dAfterOrder specifies to check whether each ship_date value is no more than 5 days after order_date.

CREATE TABLE public.store_orders_2018 (
    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 IsYear2018 CHECK (DATE_PART('year', order_date)::int = 2018),
    CONSTRAINT Ship5dAfterOrder CHECK (DAYOFYEAR(ship_date) - DAYOFYEAR(order_date) <=5)
);

When Vertica checks the data in store_orders_2018 for constraint violations, it evaluates the values of order_date and ship_date on each row, to determine whether they comply with their respective check constraints.

Check expressions

A check expression can only reference the current table row; it cannot access data stored in other tables or database objects, such as sequences. It also cannot access data in other table rows.

A check constraint expression can include:

• Arithmetic and concatenated string operators

• Logical operators such as AND, OR, NOT

• WHERE prepositions such as CASE, IN, LIKE, BETWEEN, IS [NOT] NULL

• Calls to the following function types:

  • Immutable built-in SQL functions such as LENGTH

  • Immutable SQL macros (see Check Constraints and SQL Macros below)

  • User-defined scalar functions that are marked as immutable in the component factory (see Check Constraints and UDSFs below)

Example check expressions

The following check expressions assume that the table contains all referenced columns and that they have the appropriate data types:

• CONSTRAINT chk_pos_quant CHECK (quantity > 0)

• CONSTRAINT chk_pqe CHECK (price*quantity = extended_price)

• CONSTRAINT size_sml CHECK (size in ('s', 'm', 'l', 'xl'))

• CHECK ( regexp_like(dept_name, '^[a-z]+$', 'i') OR (dept_name = 'inside sales'))

Check expression restrictions

A check expression must evaluate to a Boolean value. However, Vertica does not support implicit conversions to Boolean values. For example, the following check expressions are invalid:

• CHECK (1)

• CHECK ('hello')

A check expression cannot include the following elements:

• Subqueries—for example, CHECK (dept_id in (SELECT id FROM dept))

• Aggregates—for example, CHECK (quantity < sum(quantity)/2)

• Window functions—for example, CHECK (RANK() over () < 3)

• SQL meta-functions—for example, CHECK (START_REFRESH('') = 0)

• References to the epoch column

• References to other tables or objects (for example, sequences), or system context

• Invocation of functions that are not immutable in time and space

Enforcing check constraints

You can enforce check constraints globally with configuration parameter EnableNewCheckConstraintsByDefault. You an also enforce check constraints for specific tables by qualifying unique constraints with the keyword ENABLED. In both cases, Vertica evaluates check constraints as new values are loaded into the table, and returns with errors on any constraint violations. Alternatively, you can use ANALYZE_CONSTRAINTS to validate check constraints after updating the table contents. For details, see Constraint enforcement.

For example, you can enable the constraints shown earlier with ALTER TABLE...ALTER CONSTRAINT:

=> ALTER TABLE store_orders_2018 ALTER CONSTRAINT IsYear2018 ENABLED;
ALTER TABLE
=> ALTER TABLE store_orders_2018 ALTER CONSTRAINT Ship5dAfterOrder ENABLED;
ALTER TABLE

Check constraints and nulls

If a check expression evaluates to unknown for a given row because a column within the expression contains a null, the row passes the constraint condition. Vertica evaluates the expression and considers it satisfied if it resolves to either true or unknown. For example, check (quantity > 0) passes validation if quantity is null. This result differs from how a WHERE clause works. With a WHERE clause, the row would not be included in the result set.

You can prohibit nulls in a check constraint by explicitly including a null check in the check constraint expression. For example: CHECK (quantity IS NOT NULL AND (quantity > 0))

Check constraints and SQL macros

A check constraint can call a SQL macro (a function written in SQL) if the macro is immutable. An immutable macro always returns the same value for a given set of arguments.

When a DDL statement specifies a macro in a check expression, Vertica determines if it is immutable. If it is not, Vertica rolls back the statement.

The following example creates the macro mycompute and then uses it in a check expression:

=> CREATE OR REPLACE FUNCTION mycompute(j int, name1 varchar)
RETURN int AS BEGIN RETURN (j + length(name1)); END;
=> ALTER TABLE sampletable
ADD CONSTRAINT chk_compute
CHECK(mycompute(weekly_hours, name1))<50);

Check constraints and UDSFs

A check constraint can call user-defined scalar functions (UDSFs). The following requirements apply:

• The UDSF must be marked as immutable in the UDx factory.

• The constraint handles null values properly.

For a usage example, see C++ example: calling a UDSF from a check constraint.

5 - NOT NULL constraints

A NOT NULL constraint specifies that a column cannot contain a null value. All table updates must specify values in columns with this constraint. You can set a NOT NULL constraint on columns when you create a table, or set the constraint on an existing table with ALTER TABLE.

The following CREATE TABLE statement defines three columns as NOT NULL. You cannot store any NULL values in those columns.

=> CREATE TABLE inventory ( date_key INTEGER NOT NULL,
                            product_key INTEGER NOT NULL,
                            warehouse_key INTEGER NOT NULL, ... );

The following ALTER TABLE statement defines column sku_number in table product_dimensions as NOT NULL:

=> ALTER TABLE public.product_dimension ALTER COLUMN sku_number SET NOT NULL;
ALTER TABLE

Enforcing NOT NULL constraints

You cannot enable enforcement of a NOT NULL constraint. You must use ANALYZE_CONSTRAINTS to determine whether column data contains null values, and then manually fix any constraint violations that the function finds.

NOT NULL and primary keys

When you define a primary key, Vertica automatically sets the primary key columns 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.