ANALYZE_CONSTRAINTS
Analyzes and reports on constraint violations within the specified scope
You can enable automatic enforcement of primary key, unique key, and check constraints when INSERT
, UPDATE
, MERGE
, or COPY
statements execute. Alternatively, you can use ANALYZE_CONSTRAINTS
to validate constraints after issuing these statements. Refer to Constraint Enforcement for more information.
ANALYZE_CONSTRAINTS
performs a lock in the same way that SELECT * FROM t1
holds a lock on table t1
. See LOCKS
for additional information.
Syntax
ANALYZE_CONSTRAINTS ('[[[database.]schema.]table ]' [, 'column[,…]'] )
Parameters
[database.]schema
|
Specifies a schema, by default myschema.thisDbObject If you specify a database, it must be the current database. |
table
|
Identifies the table to analyze. If you omit specifying a schema, Vertica uses the current schema search path. If set to an empty string, Vertica analyzes all tables in the current schema. |
column |
The column in table to analyze. You can specify multiple comma-delimited columns. Vertica narrows the scope of the analysis to the specified columns. If you omit specifying a column, Vertica analyzes all columns in table. |
Privileges
- Schema: USAGE
- Table: SELECT
Detecting Constraint Violations During a Load Process
Vertica checks for constraint violations when queries are run, not when data is loaded. To detect constraint violations as part of the load process, use a COPY statement with the NO COMMIT option. By loading data without committing it, you can run a post-load check of your data using the ANALYZE_CONSTRAINTS
function. If the function finds constraint violations, you can roll back the load because you have not committed it.
If ANALYZE_CONSTRAINTS
finds violations, such as when you insert a duplicate value into a primary key, you can correct errors using the following functions. Effects last until the end of the session only:
If a check constraint SQL expression evaluates to an unknown for a given row because a column within the expression contains a null, the row passes the constraint condition.
Return Values
ANALYZE_CONSTRAINTS
returns results in a structured set (see table below) that lists the schema name, table name, column name, constraint name, constraint type, and the column values that caused the violation.
If the result set is empty, then no constraint violations exist; for example:
> SELECT ANALYZE_CONSTRAINTS ('public.product_dimension', 'product_key'); Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values -------------+------------+--------------+-----------------+-----------------+--------------- (0 rows)
The following result set shows a primary key violation, along with the value that caused the violation ('10')
:
=> SELECT ANALYZE_CONSTRAINTS (''); Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values -------------+------------+--------------+-----------------+-----------------+--------------- store t1 c1 pk_t1 PRIMARY ('10') (1 row)
The result set columns are described in further detail in the following table:
Column Name | Data Type | Description |
---|---|---|
Schema Name |
VARCHAR |
The name of the schema. |
Table Name |
VARCHAR |
The name of the table, if specified. |
Column Names |
VARCHAR |
A list of comma-delimited columns that contain constraints. |
Constraint Name |
VARCHAR |
The given name of the primary key, foreign key, unique, check, or not null constraint, if specified. |
Constraint Type |
VARCHAR |
Identified by one of the following strings:
|
Column Values |
VARCHAR |
Value of the constraint column, in the same order in which When interpreted as SQL, the value of this column forms a list of values of the same type as the columns in
|
Examples
See Detecting Constraint Violations in the Administrator's Guide.