This is the multi-page printable view of this section.
Click here to print.
Return to the regular view of this page.
Constraints
Constraints set rules on what data is allowed in table columns.
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.
You set constraints on a new table and an existing one with
CREATE TABLE
and
ALTER TABLE...ADD CONSTRAINT
, respectively.
To view current constraints, see Column-constraint.
1 - Supported constraints
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:
=> 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.
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:
=> 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 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.
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:
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:
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))
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);
Check constraints and UDSFs
A check constraint can call user-defined scalar functions (UDSFs). The following requirements apply:
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.
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.
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.
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:
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:
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
);
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:
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.
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:
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:
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:
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.
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:.
You can enforce the following constraints:
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:
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:.
Constraints can be enforced at two levels:
Constraint enforcement parameters
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
:
=> 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.
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:
=> 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 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.
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.
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:
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:
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.
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:
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.
Note
You can always use ALTER TABLE...DROP CONSTRAINT
to disable primary and unique key constraints in local and global temporary tables.