Deciding Whether to Enable Primary Key, Unique Key, and Check Constraints
You have the option to choose automatic enforcement of primary key, unique key, and check constraints. Depending upon your specific scenario, you can either enable this feature, or use ANALYZE_CONSTRAINTS to validate constraints. Consider these factors:
- Benefits of Enabling Primary Key, Unique Key, and Check Constraints
- Considerations Before Enabling Constraints
- Where Constraints Are Enforced
- Impact of Floating Point Values in Primary Keys When Using Automatic Enforcement
- Constraint Enforcement Limitations
For more information on using ANALYZE_CONSTRAINTS and how automatic enforcement differs, see the Administrator's Guide section, Detecting Constraint Violations with ANALYZE_CONSTRAINTS.
Benefits of Enabling Primary Key, Unique Key, and Check Constraints
When you enable primary key, unique key, or check constraints, Vertica validates data before it is inserted. Because you do not need to validate data using ANALYZE_CONSTRAINTS after insertion, query speed improves.
Having enabled key constraints, particularly on primary keys, can help the optimizer produce faster query plans, particularly for joins. When a table has an enabled primary key constraint, the optimizer can assume that it has no rows with duplicate values across the key set.
Vertica automatically creates special purpose projections, if necessary, to enforce enabled key constraints. In some cases Vertica can use an existing projection instead.
Considerations Before Enabling Constraints
Multiple factors affect performance. The enforcement process can slow DML and bulk loading.
If you are doing bulk loads, consider the size of your tables and the number of columns in your keys. You could decide to disable automatic enforcement for fact tables, which tend to be larger, but enable enforcement for dimension tables. For fact tables, you could choose manual constraint validation using ANALYZE_CONSTRAINTS, and avoid the load-time overhead of automatic validation.
When you enable automatic enforcement of primary key, unique key, or check constraints, statement rollbacks occur if validation fails during DML. Vertica completely rolls back the statement causing the failure. When deciding to enable automatic enforcement of constraints, consider the impact of statements rolling back on violations. For example, you issue ten insert statements, none of which have committed. If the sixth statement introduces a duplicate, that statement is rolled back. The other statements that do not introduce duplicates can commit.
Note: Vertica performs primary key, unique key, and check constraint enforcement at the SQL statement level rather than the transaction level. You cannot defer enforcement until transaction commit.
Where Constraints Are Enforced
Automatic enforcement of constraints occurs in:
- INSERT statements — Both in single row insertions, and in an INSERT statement that includes the SELECT parameter.
- Bulk loads — On bulk loads that use the COPY statement.
- UPDATE or MERGE statements — All UPDATE and MERGE statements.
- Meta functions — On COPY_PARTITIONS_TO_TABLE, MOVE_PARTITIONS_TO_TABLE and SWAP_PARITIONS_BETWEEN_TABLES.
- ALTER TABLE statements — On statements that include either the ADD CONSTRAINT or ALTER CONSTRAINT parameters where you are enabling a constraint and the table has existing data.
Impact of Floating Point Values in Primary Keys When Using Automatic Enforcement
Vertica allows NaN, +Inf, and -Inf values in a FLOAT type column, even if the column is part of a primary key. Because FLOAT types provide imprecise arithmetic, Vertica recommends that you not use columns with floating point values within primary keys.
If you do decide to use a FLOAT type within a primary key, note the following in regards to primary key enforcement. (This behavior is the same regardless of whether you enable an automatic constraint or check constraints manually with ANALYZE_CONSTRAINTS.)
-
For the purpose of enforcing key constraints, Vertica considers two NaNs, (or two +Inf, or two –Inf) values to be equal.
-
If a table has an enabled single column primary key constraint of type FLOAT, only one tuple can have a NaN value for the column. Otherwise, the constraint is violated. This is also true for +Inf and –Inf values. Note that this differs from the IEEE 754 standard, which specifies that multiple NaN values are different from each other.
-
A join on a single column that contains FLOAT values fails if the table that includes a primary key contains multiple tuples with two NaNs (or +Inf, or –Inf) values.
For information on floating point type, see DOUBLE PRECISION (FLOAT).
Constraint Enforcement Limitations
You can only enable or disable automatic enforcement for primary key, unique key, and check constraints. Vertica does not support automatic enforcement of foreign keys and referential integrity. You can manually validate foreign key constraints using the meta-function ANALYZE_CONSTRAINTS.
Vertica does not support automatic enforcement of constraints on external tables.