Constraints

• 1: Supported constraints
• 1.1: Primary key constraints
• 1.2: Foreign key constraints
• 1.3: Unique constraints
• 1.4: Check constraints
• 1.5: NOT NULL constraints
• 2: Setting constraints
• 3: Dropping constraints
• 4: Naming constraints
• 5: Detecting constraint violations
• 6: Constraint enforcement
• 6.1: Levels of constraint enforcement
• 6.2: Verifying constraint enforcement
• 6.3: Reporting constraint violations
• 6.4: Constraint enforcement and locking
• 6.5: Constraint enforcement and performance
• 6.6: Projections for enforced constraints
• 6.7: Constraint enforcement limitations

1 - Supported constraints

Vertica supports standard SQL constraints, as described in this section.

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

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

1.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
);

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

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

2 - Setting constraints

You can set constraints on a new table and an existing one with CREATE TABLE and ALTER TABLE...ADD CONSTRAINT, respectively.

Setting constraints on a new table

CREATE TABLE can specify a constraint in two ways: as part of the column definition, or following all column definitions.

For example, the following CREATE TABLE statement sets two constraints on column sku_number, NOT NULL and UNIQUE. After all columns are defined, the statement also sets a primary key that is composed of two columns, product_key and product_version:

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

Setting constraints on an existing table

ALTER TABLE...ADD CONSTRAINT adds a constraint to an existing table. For example, the following statement specifies unique values for column product_version:

=> ALTER TABLE prod_dimension ADD CONSTRAINT u_product_versions UNIQUE (product_version) ENABLED;
ALTER TABLE

Validating existing data

When you add a constraint on a column that already contains data, Vertica immediately validates column values if the following conditions are both true:

• The constraint is a primary key, unique, or check constraint.

• The constraint is enforced.

If either of these conditions is not true, Vertica does not validate the column values. In this case, you must call ANALYZE_CONSTRAINTS to find constraint violations. Otherwise, queries are liable to return unexpected results. For details, see Detecting constraint violations.

Exporting table constraints

Whether you specify constraints in the column definition or on the table, Vertica stores the table DDL as part of the CREATE statement and exports them as such. One exception applies: foreign keys are stored and exported as ALTER TABLE statements.

For example:

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

3 - Dropping constraints

ALTER TABLE drops constraints from tables in two ways:

• ALTER TABLE...DROP CONSTRAINT removes a named table constraint.

• ALTER TABLE...ALTER COLUMN removes a column's NOT NULL constraint.

For example, table store_orders_2018 specifies the following constraints:

• Named constraint pk identifies column order_no as a primary key.

• Named constraint IsYear2018 specifies a check constraint that allows only 2018 dates in column order_date.

• Named constraint Ship5dAfterOrder specifies a check constraint that disallows any ship_date value that is more than 5 days after order_date.

• Columns order_no and order_date are set to NOT NULL.

CREATE TABLE public.store_orders_2018 (
    order_no int NOT NULL 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)
);

Dropping named constraints

You remove primary, foreign key, check, and unique constraints with ALTER TABLE...DROP CONSTRAINT, which requires you to supply their names. For example, you remove the primary key constraint in table store_orders_2018 as follows:

=> ALTER TABLE store_orders_2018 DROP CONSTRAINT pk;
ALTER TABLE
=> SELECT export_tables('','store_orders_2018');
                                                             export_tables
---------------------------------------------------------------------------------------------------------------------------------------

CREATE TABLE public.store_orders_2018
(
    order_no int NOT NULL,
    product_key int,
    product_version int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date,
    CONSTRAINT IsYear2018 CHECK (((date_part('year', store_orders_2018.order_date))::int = 2018)) ENABLED,
    CONSTRAINT Ship5dAfterOrder CHECK (((dayofyear(store_orders_2018.ship_date) - dayofyear(store_orders_2018.order_date)) <= 5)) ENABLED
);

Dropping NOT NULL constraints

You drop a column's NOT NULL constraint with ALTER TABLE...ALTER COLUMN, as in the following example:

=> ALTER TABLE store_orders_2018 ALTER COLUMN order_date DROP NOT NULL;
ALTER TABLE

Dropping primary keys

You cannot drop a primary key constraint if another table has a foreign key constraint that references the primary key. To drop the primary key, you must first drop all foreign keys that reference it.

Dropping constraint-referenced columns

If you try to drop a column that is referenced by a constraint in the same table, the drop operation returns with an error. For example, check constraint Ship5dAfterOrder references two columns, order_date and ship_date. If you try to drop either column, Vertica returns the following error message:

=> ALTER TABLE public.store_orders_2018 DROP COLUMN ship_date;
ROLLBACK 3128:  DROP failed due to dependencies
DETAIL:
Constraint Ship5dAfterOrder references column ship_date
HINT:  Use DROP .. CASCADE to drop or modify the dependent objects

In this case, you must qualify the DROP COLUMN clause with the CASCADE option, which specifies to drop the column and its dependent objects—in this case, constraint Ship5dAfterOrder:

=> ALTER TABLE public.store_orders_2018 DROP COLUMN ship_date CASCADE;
ALTER TABLE

A call to Vertica function EXPORT_TABLES confirms that the column and the constraint were both removed:

=> ALTER TABLE public.store_orders_2018 DROP COLUMN ship_date CASCADE;
ALTER TABLE
dbadmin=> SELECT export_tables('','store_orders_2018');
                                              export_tables
---------------------------------------------------------------------------------------------------------

CREATE TABLE public.store_orders_2018
(
    order_no int NOT NULL,
    product_key int,
    product_version int,
    order_date timestamp,
    shipper varchar(20),
    CONSTRAINT IsYear2018 CHECK (((date_part('year', store_orders_2018.order_date))::int = 2018)) ENABLED
);

(1 row)

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

5 - Detecting constraint violations

ANALYZE_CONSTRAINTS analyzes and reports on table constraint violations within a given schema. You can use ANALYZE_CONSTRAINTS to analyze an individual table, specific columns within a table, or all tables within a schema. You typically use this function on tables where primary key, unique, or check constraints are not enforced. You can also use ANALYZE_CONSTRAINTS to check the referential integrity of foreign keys.

In the simplest use case, ANALYZE_CONSTRAINTS is a two-step process:

1. Run ANALYZE_CONSTRAINTS on the desired table. ANALYZE_CONSTRAINTS reports all rows that violate constraints.

2. Use the report to fix violations.

You can also use ANALYZE_CONSTRAINTS in the following cases:

• Analyze tables with enforced constraints.

• Detect constraint violations introduced by a COPY operation and address them before the copy transaction is committed.

Analyzing tables with enforced constraints

If constraints are enforced on a table and a DML operation returns constraint violations, Vertica reports on a limited number of constraint violations before it rolls back the operation. This can be problematic when you try to load a large amount of data that includes many constraint violations—for example, duplicate key values. In this case, use ANALYZE_CONSTRAINTS as follows:

1. Temporarily disable enforcement of all constraints on the target table.

2. Run the DML operation.

3. After the operation returns, run ANALYZE_CONSTRAINTS on the table. ANALYZE_CONSTRAINTS reports all rows that violate constraints.

4. Use the report to fix the violations.

5. Re-enable constraint enforcement on the table.

Using ANALYZE_CONSTRAINTS in a COPY transaction

Use ANALYZE_CONSTRAINTS to detect and address constraint violations introduced by a COPY operation as follows:

1. Copy the source data into the target table with COPY...NO COMMIT.

2. Call ANALYZE_CONSTRAINTS to check the target table with its uncommitted updates.

3. If ANALYZE_CONSTRAINTS reports constraint violations, roll back the copy transaction.

4. Use the report to fix the violations, and then re-execute the copy operation.

For details about using COPY...NO COMMIT, see Using transactions to stage a load.

Distributing constraint analysis

ANALYZE_CONSTRAINTS runs as an atomic operation—that is, it does not return until it evaluates all constraints within the specified scope. For example, if you run ANALYZE_CONSTRAINTS against a table, the function returns only after it evaluates all column constraints against column data. If the table has a large number of columns with constraints, and contains a very large data set, ANALYZE_CONSTRAINTS is liable to exhaust all available memory and return with an out-of-memory error. This risk is increased by running ANALYZE_CONSTRAINTS against multiple tables simultaneously, or against the entire database.

You can minimize the risk of out-of-memory errors by setting configuration parameter MaxConstraintChecksPerQuery (by default set to -1) to a positive integer. For example, if this parameter is set to 20, and you run ANALYZE_CONSTRAINTS on a table that contains 38 column constraints, the function divides its work into two separate queries. ANALYZE_CONSTRAINTS creates a temporary table for loading and compiling results from the two queries, and then returns the composite result set.

MaxConstraintChecksPerQuery can only be set at the database level, and can incur a certain amount of overhead. When set, commits to the temporary table created by ANALYZE_CONSTRAINTS cause all pending database transactions to auto-commit. Setting this parameter to a reasonable number such as 20 should minimize its performance impact.

6 - Constraint enforcement

You can enforce the following constraints:

• PRIMARY KEY

• UNIQUE

• CHECK

When you enable constraint enforcement on a table, Vertica applies that constraint immediately to the table's current content, and to all content that is added or updated later.

Operations that invoke constraint enforcement

The following DDL and DML operations invoke constraint enforcement:

• ALTER TABLE...ADD CONSTRAINT and ALTER TABLE...ALTER CONSTRAINT

• INSERT and INSERT...SELECT

• COPY
• UPDATE
• MERGE

• Partitioning functions:
  - COPY_PARTITIONS_TO_TABLE
  - MOVE_PARTITIONS_TO_TABLE
  - SWAP_PARTITIONS_BETWEEN_TABLES

Benefits and costs

Enabling constraint enforcement can help minimize post-load maintenance tasks, such as validating data separately with ANALYZE_CONSTRAINTS, and then dealing with the constraint violations that it returns.

Enforcing key constraints, particularly on primary keys, can help the optimizer produce faster query plans, particularly for joins. When a primary key constraint is enforced on a table, the optimizer assumes that no rows in that table contain duplicate key values.

Under certain circumstances, widespread constraint enforcement, especially in large fact tables, can incur significant system overhead. For details, see Constraint enforcement and performance.

6.1 - Levels of constraint enforcement

Constraints can be enforced at two levels:

• Database configuration parameters

• Table constraints

Constraint enforcement parameters

Vertica supports three Boolean parameters to enforce constraints:

Table constraint enforcement

You set constraint enforcement on tables through CREATE TABLE and ALTER TABLE, by qualifying the constraints with the keywords ENABLED or DISABLED. The following CREATE TABLE statement enables enforcement of a check constraint in its definition of column order_qty:

=> CREATE TABLE new_orders (
   cust_id int,
   order_date timestamp DEFAULT CURRENT_TIMESTAMP,
   product_id varchar(12),
   order_qty int CHECK(order_qty > 0) ENABLED,
   PRIMARY KEY(cust_id, order_date) ENABLED
);
CREATE TABLE

ALTER TABLE can enable enforcement on existing constraints. The following statement modifies table customer_dimension by enabling enforcement on named constraint C_UNIQUE:

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

Enforcement level precedence

Table and column enforcement settings have precedence over enforcement parameter settings. If a table or column constraint omits ENABLED or DISABLED, Vertica uses the current settings of the pertinent configuration parameters.

The following CREATE TABLE statement creates table new_sales with columns order_id and order_qty, which are defined with constraints PRIMARY KEY and CHECK, respectively:

=> CREATE TABLE new_sales ( order_id int PRIMARY KEY, order_qty int CHECK (order_qty > 0) );

Neither constraint is explicitly enabled or disabled, so Vertica uses configuration parameters EnableNewPrimaryKeysByDefault and EnableNewCheckConstraintsByDefault to set enforcement in the table definition:

=> SHOW CURRENT EnableNewPrimaryKeysByDefault, EnableNewCheckConstraintsByDefault;
  level  |                name                | setting
---------+------------------------------------+---------
 DEFAULT | EnableNewPrimaryKeysByDefault      | 0
 DEFAULT | EnableNewCheckConstraintsByDefault | 1
(2 rows)

=> SELECT EXPORT_TABLES('','new_sales');
...

CREATE TABLE public.new_sales
(
    order_id int NOT NULL,
    order_qty int,
    CONSTRAINT C_PRIMARY PRIMARY KEY (order_id) DISABLED,
    CONSTRAINT C_CHECK CHECK ((new_sales.order_qty > 0)) ENABLED
);

(1 row)

In this case, changing EnableNewPrimaryKeysByDefault to 1 (enabled) has no effect on the C_PRIMARY constraint in table new_sales. You can enforce this constraint with ALTER TABLE...ALTER CONSTRAINT:

=> ALTER TABLE public.new_sales ALTER CONSTRAINT C_PRIMARY ENABLED;
ALTER TABLE

6.2 - Verifying constraint enforcement

SHOW CURRENT can return the settings of constraint enforcement parameters:

=> SHOW CURRENT EnableNewCheckConstraintsByDefault, EnableNewUniqueKeysByDefault, EnableNewPrimaryKeysByDefault;
  level   |                name                | setting
----------+------------------------------------+---------
 DEFAULT  | EnableNewCheckConstraintsByDefault | 1
 DEFAULT  | EnableNewUniqueKeysByDefault       | 0
 DATABASE | EnableNewPrimaryKeysByDefault      | 1
(3 rows)

You can also query the following system tables to check table enforcement settings:

• CONSTRAINT_COLUMNS
• TABLE_CONSTRAINTS
• PRIMARY_KEYS

For example, the following statement queries TABLE_CONSTRAINTS and returns all constraints in database tables. Column is_enabled is set to true or false for all constraints that can be enabled or disabled—PRIMARY KEY, UNIQUE, and CHECK:

=> SELECT constraint_name, table_name, constraint_type, is_enabled FROM table_constraints ORDER BY is_enabled, table_name;
      constraint_name      |      table_name       | constraint_type | is_enabled
---------------------------+-----------------------+-----------------+------------
 C_PRIMARY                 | call_center_dimension | p               | f
 C_PRIMARY                 | date_dimension        | p               | f
 C_PRIMARY                 | employee_dimension    | p               | f
 C_PRIMARY                 | online_page_dimension | p               | f
 C_PRIMARY                 | product_dimension     | p               | f
 C_PRIMARY                 | promotion_dimension   | p               | f
 C_PRIMARY                 | shipping_dimension    | p               | f
 C_PRIMARY                 | store_dimension       | p               | f
 C_UNIQUE_1                | tabletemp             | u               | f
 C_PRIMARY                 | vendor_dimension      | p               | f
 C_PRIMARY                 | warehouse_dimension   | p               | f
 C_PRIMARY                 | customer_dimension    | p               | t
 C_PRIMARY                 | new_sales             | p               | t
 C_CHECK                   | new_sales             | c               | t
 fk_inventory_date         | inventory_fact        | f               |
 fk_inventory_product      | inventory_fact        | f               |
 fk_inventory_warehouse    | inventory_fact        | f               |
 ...

The following query returns all tables that have primary key, unique, and check constraints, and shows whether the constraints are enabled:

=> SELECT table_name, constraint_name, constraint_type, is_enabled FROM constraint_columns
   WHERE constraint_type in ('p', 'u', 'c')
   ORDER BY table_name, constraint_type;

=> SELECT table_name, constraint_name, constraint_type, is_enabled FROM constraint_columns WHERE constraint_type in ('p', 'u', 'c') ORDER BY table_name, constraint_type;
      table_name       | constraint_name | constraint_type | is_enabled
-----------------------+-----------------+-----------------+------------
 call_center_dimension | C_PRIMARY       | p               | f
 customer_dimension    | C_PRIMARY       | p               | t
 customer_dimension2   | C_PRIMARY       | p               | t
 customer_dimension2   | C_PRIMARY       | p               | t
 date_dimension        | C_PRIMARY       | p               | f
 employee_dimension    | C_PRIMARY       | p               | f
 new_sales             | C_CHECK         | c               | t
 new_sales             | C_PRIMARY       | p               | t
 ...

6.3 - Reporting constraint violations

Vertica reports constraint violations in two cases:

• ALTER TABLE tries to enable constraint enforcement on a table that already contains data, and the data does not comply with the constraint.

• A DML operation tries to add or update data on a table with enforced constraints, and the new data does not comply with one or more constraints.

DDL constraint violations

When you enable constraint enforcement on an existing table with ALTER TABLE...ADD CONSTRAINT or ALTER TABLE...ALTER CONSTRAINT, Vertica applies that constraint immediately to the table's current content. If Vertica detects constraint violations, Vertica returns with an error that reports on violations and then rolls back the ALTER TABLE statement.

For example:

=> ALTER TABLE public.customer_dimension ADD CONSTRAINT unique_cust_types UNIQUE (customer_type) ENABLED;
ERROR 6745:  Duplicate key values: 'customer_type=Company'
-- violates constraint 'public.customer_dimension.unique_cust_types'
DETAIL:  Additional violations:
Constraint 'public.customer_dimension.unique_cust_types':
duplicate key values: 'customer_type=Individual'

DML constraint violations

When you invoke DML operations that add or update data on a table with enforced constraints, Vertica checks that the new data complies with these constraints. If Vertica detects constraint violations, the operation returns with an error that reports on violations, and then rolls back.

For example, table store_orders and store_orders_2015 are defined with the same primary key and check constraints. Both tables enable enforcement of the primary key constraint; only store_orders_2015 enforces the check constraint:

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

ALTER TABLE public.store_orders ADD CONSTRAINT C_PRIMARY PRIMARY KEY (order_no) ENABLED;
ALTER TABLE public.store_orders ADD CONSTRAINT C_CHECK CHECK (((date_part('year', store_orders.order_date))::int = 2014)) DISABLED;

CREATE TABLE public.store_orders_2015
(
    order_no int NOT NULL,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
)
PARTITION BY ((date_part('year', store_orders_2015.order_date))::int);

ALTER TABLE public.store_orders_2015 ADD CONSTRAINT C_PRIMARY PRIMARY KEY (order_no) ENABLED;
ALTER TABLE public.store_orders_2015 ADD CONSTRAINT C_CHECK CHECK (((date_part('year', store_orders_2015.order_date))::int = 2015)) ENABLED;

If you try to insert data with duplicate key values into store_orders, the insert operation returns with an error message. The message contains detailed information about the first violation. It also returns abbreviated information about subsequent violations, up to the first 30. If necessary , the error message also includes a note that more than 30 violations occurred:

=> INSERT INTO store_orders SELECT order_number, date_ordered, shipper_name, date_shipped FROM store.store_orders_fact;
ERROR 6745:  Duplicate key values: 'order_no=10' -- violates constraint 'public.store_orders.C_PRIMARY'
DETAIL:  Additional violations:
Constraint 'public.store_orders.C_PRIMARY':
duplicate key values:
'order_no=11'; 'order_no=12'; 'order_no=13'; 'order_no=14'; 'order_no=15'; 'order_no=17';
'order_no=21'; 'order_no=23'; 'order_no=26'; 'order_no=27'; 'order_no=29'; 'order_no=33';
'order_no=35'; 'order_no=38'; 'order_no=39'; 'order_no=4'; 'order_no=41'; 'order_no=46';
'order_no=49'; 'order_no=6'; 'order_no=62'; 'order_no=67'; 'order_no=68'; 'order_no=70';
'order_no=72'; 'order_no=75'; 'order_no=76'; 'order_no=77'; 'order_no=79';
Note: there were additional errors

Similarly, the following attempt to copy data from store_orders into store_orders_2015 violates the table's check constraint. It returns with an error message like the one shown earlier:

=> SELECT COPY_TABLE('store_orders', 'store_orders_2015');
NOTICE 7636:  Validating enabled constraints on table 'public.store_orders_2015'...
ERROR 7231:  Check constraint 'public.store_orders_2015.C_CHECK' ((date_part('year', store_orders_2015.order_date))::int = 2015)
violation in table 'public.store_orders_2015': 'order_no=101,order_date=2007-05-02 00:00:00'
DETAIL:  Additional violations:
Check constraint 'public.store_orders_2015.C_CHECK':violations:
'order_no=106,order_date=2016-07-01 00:00:00'; 'order_no=119,order_date=2016-01-04 00:00:00';
'order_no=14,order_date=2016-07-01 00:00:00'; 'order_no=154,order_date=2016-11-06 00:00:00';
'order_no=156,order_date=2016-04-10 00:00:00'; 'order_no=171,order_date=2016-10-08 00:00:00';
'order_no=203,order_date=2016-03-01 00:00:00'; 'order_no=204,order_date=2016-06-09 00:00:00';
'order_no=209,order_date=2016-09-07 00:00:00'; 'order_no=214,order_date=2016-11-02 00:00:00';
'order_no=223,order_date=2016-12-08 00:00:00'; 'order_no=227,order_date=2016-08-02 00:00:00';
'order_no=240,order_date=2016-03-09 00:00:00'; 'order_no=262,order_date=2016-02-09 00:00:00';
'order_no=280,order_date=2016-10-10 00:00:00';
Note: there were additional errors

Partition management functions that add or update table content must also respect enforced constraints in the target table. For example, the following MOVE_PARTITIONS_TO_TABLE operation attempts to move a partition from store_orders into store_orders_2015. However, the source partition includes data that violates the target table's check constraint. Thus, the function returns with results that indicate it failed to move any data:

=> SELECT MOVE_PARTITIONS_TO_TABLE ('store_orders','2014','2014','store_orders_2015');
NOTICE 7636:  Validating enabled constraints on table 'public.store_orders_2015'...
             MOVE_PARTITIONS_TO_TABLE
--------------------------------------------------
 0 distinct partition values moved at epoch 204.

6.4 - Constraint enforcement and locking

Vertica uses an insert/validate (IV) lock for DML operations that require validation for enabled primary key and unique constraints.

When you run these operations on tables that enforce primary or unique key constraints, Vertica sets locks on the tables as follows:

1. Sets an I (insert) lock in order to load data. Multiple sessions can acquire an I lock on the same table simultaneously, and load data concurrently.

2. Sets an IV lock on the table to validate the loaded data against table primary and unique constraints. Only one session at a time can acquire an IV lock on a given table. Other sessions that need to access this table are blocked until the IV lock is released. A session retains its IV lock until one of two events occur:

   • Validation is complete and the DML operation is committed.

   • A constraint violation is detected and the operation is rolled back.

   In either case, Vertica releases the IV lock.

IV lock blocking

While Vertica validates a table's primary or unique key constraints, it temporarily blocks other DML operations on the table. These delays can be especially noticeable when multiple sessions concurrently try to perform extensive changes to data on the same table.

For example, each of three concurrent sessions attempts to load data into table t1, as follows:

1. All three session acquire an I lock on t1 and begin to load data into the table.

2. Session 2 acquires an exclusive IV lock on t1 to validate table constraints on the data that it loaded. Only one session at a time can acquire an IV lock on a table, so sessions 1 and 3 must wait for session 2 to complete validation before they can begin their own validation.

3. Session 2 successfully validates all data that it loaded into t1. On committing its load transaction, it releases its IV lock on the table.

4. Session 1 acquires an IV lock on t1 and begins to validate the data that it loaded. In this case, Vertica detects a constraint violation and rolls back the load transaction. Session 1 releases its IV lock on t1.

5. Session 3 now acquires an IV lock on t1 and begins to validate the data that it loaded. On completing validation, session 3 commits its load transaction and releases the IV lock on t1. The table is now available for other DML operations.

See also

For information on lock modes and compatibility and conversion matrices, see Lock modes. See also LOCKS and LOCK_USAGE.

6.5 - Constraint enforcement and performance

In some cases, constraint enforcement can significantly affect overall system performance. This is especially true when constraints are enforced on large fact tables that are subject to frequent and concurrent bulk updates. Every update operation that invokes constraint enforcement requires Vertica to check each table row for all constraint violations. Thus, enforcing multiple constraints on a table with a large amount of data can cause noticeable delays.

To minimize the overhead incurred by enforcing constraints, omit constraint enforcement on large, often updated tables. You can evaluate these tables for constraint violations by running ANALYZE_CONSTRAINTS during off-peak hours.

Several aspects of constraint enforcement have specific impact on system performance. These include:

• Table locking

• Projections for enforced constraints

• Constraint-triggered rollback within transactions

Table locking

If a table enforces constraints, Vertica sets an insert/validate (IV) lock on that table during a DML operation while it undergoes validation. Only one session at a time can acquire an IV lock on that table. As long as the session retains this lock, no other session can access the table. Lengthy loads is liable to cause performance bottlenecks, especially if multiple sessions try to load the same table simultaneously. For details, see Constraint enforcement and locking.

Enforced constraint projections

To enforce primary key and unique constraints, Vertica creates special projections that it uses to validate data. Depending on the amount of data in the anchor table, creating the projection might incur significant system overhead.

Rollback in transactions

Vertica validates enforced constraints for each SQL statement, and rolls back each statement that encounters a constraint violation. You cannot defer enforcement until the transaction commits. Thus, if multiple DML statements comprise a single transaction, Vertica validates each statement separately for constraint compliance, and rolls back any statement that fails validation. It commits the transaction only after all statements in it return.

For example, you might issue ten INSERT statements as a single transaction on a table that enforces UNIQUE on one of its columns. If the sixth statement attempts to insert a duplicate value in that column, that statement is rolled back. However, the other statements can commit.

6.6 - Projections for enforced constraints

To enforce primary key and unique constraints, Vertica creates special constraint enforcement projections that it uses to validate new and updated data. If you add a constraint on an empty table, Vertica creates a constraint enforcement projection for that table only when data is added to it. If you add a primary key or unique constraint to a populated table and enable enforcement, Vertica chooses an existing projection to enforce the constraint, if one exists. Otherwise, Vertica creates a projection for that constraint. If a constraint violation occurs, Vertica rolls back the statement and any projection it created for the constraint.

If you drop an enforced primary key or unique constraint, Vertica automatically drops the projection associated with that constraint. You can also explicitly drop constraint projections with DROP PROJECTION. If the statement omits CASCADE, Vertica issues a warning about dropping this projection for an enabled constraint; otherwise, it silently drops the projection. In either case, the next time Vertica needs to enforce this constraint, it recreates the projection. Depending on the amount of data in the anchor table, creating the projection can incur significant overhead.

You can query system table PROJECTIONS on Boolean column IS_KEY_CONSTRAINT_PROJECTION to obtain constraint-specific projections.

6.7 - Constraint enforcement limitations

Vertica does not support constraint enforcement for foreign keys or external tables. Restrictions also apply to temporary tables.

Foreign keys

Vertica does not support enforcement of foreign keys and referential integrity. Thus, it is possible to load data that can return errors in the following cases:

• An inner join query is processed.

• An outer join is treated as an inner join due to the presence of foreign keys.

To validate foreign key constraints, use ANALYZE_CONSTRAINTS.

External tables

Vertica does not support automatic enforcement of constraints on external tables.

Local temporary tables

ALTER TABLE can set enforcement on a primary key or unique constraint in a local temporary table only if the table contains no data. If you try to enforce a constraint in a table that contains data, ALTER TABLE returns an error.

Global temporary tables

In a global temporary table, you set enforcement on a primary key or unique constraint only with CREATE TEMPORARY TABLE. ALTER TABLE returns an error if you try to set enforcement on a primary key or unique constraint in an existing table, whether populated or empty.