Constraints set rules on what data is allowed in table columns. Using constraints can help maintain data integrity. For example, you can constrain a column to allow only unique values, or to disallow NULL values. Constraints such as primary keys also help the optimizer generate query plans that facilitate faster data access, particularly for joins.
Vertica supports standard SQL constraints, as described in this section.
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 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:
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.
Tip
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
Note
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.
1.2 - Foreign key constraints
A foreign key joins a table to another table by referencing its primary key.
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:
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:
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:
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:
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 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:
A check constraint specifies a Boolean expression that evaluates a column's value on each row.
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.
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
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))
Tip
Alternatively, set a NOT NULL constraint on the same column.
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);
The UDSF must be marked as immutable in the UDx factory.
The constraint handles null values properly.
Caution
Vertica evaluates an enabled check constraint on every row that is loaded or updated. Invoking a computationally expensive check constraint on a large table is liable to incur considerable system overhead.
A NOT NULL constraint specifies that a column cannot contain a null value.
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.
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:
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:
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 drops constraints from tables in two ways:
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
);
Important
If you do not explicitly name a constraint, Vertica assigns its own name. You can obtain all constraint names from the Vertica catalog with
EXPORT_TABLES, or by querying the following system tables:
=> 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.
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:
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:
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:
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.
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:
Run ANALYZE_CONSTRAINTS on the desired table. ANALYZE_CONSTRAINTS reports all rows that violate constraints.
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:
Temporarily disable enforcement of all constraints on the target table.
Run the DML operation.
After the operation returns, run ANALYZE_CONSTRAINTS on the table. ANALYZE_CONSTRAINTS reports all rows that violate constraints.
Use the report to fix the violations.
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:
Copy the source data into the target table with COPY...NO COMMIT.
Call ANALYZE_CONSTRAINTS to check the target table with its uncommitted updates.
If ANALYZE_CONSTRAINTS reports constraint violations, roll back the copy transaction.
Use the report to fix the violations, and then re-execute the copy operation.
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:.
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:
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.
Vertica supports three Boolean parameters to enforce constraints:
Enforcement parameter
Default setting
EnableNewPrimaryKeysByDefault
0 (false/disabled)
EnableNewUniqueKeysByDefault
0 (false/disabled)
EnableNewCheckConstraintsByDefault
1 (true/enabled)
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:
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.
Important
Changing constraint enforcement parameters has no effect on existing table constraints that omit ENABLED or DISABLED. These table constraints retain the enforcement settings that they previously acquired. You can change the enforcement settings on these constraints only with
ALTER TABLE...ALTER CONSTRAINT.
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:
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 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:
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:.
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.
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:
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:
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.
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:
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.
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:
All three session acquire an I lock on t1 and begin to load data into the table.
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.
Session 2 successfully validates all data that it loaded into t1. On committing its load transaction, it releases its IV lock on the table.
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.
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.
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:
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.
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.
Note
Constraint enforcement projections in a table can significantly facilitate its analysis by
ANALYZE_CONSTRAINTS.
6.7 - Constraint enforcement limitations
Vertica does not support constraint enforcement for foreign keys or external tables.
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.
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.
Note
You can always use ALTER TABLE...DROP CONSTRAINT to disable primary and unique key constraints in local and global temporary tables.