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 public. If schema is any schema other than public, you must supply the schema name. For example:

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:

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • NOT NULL
Column Values

VARCHAR

Value of the constraint column, in the same order in which Column Names contains the value of that column in the violating row.

When interpreted as SQL, the value of this column forms a list of values of the same type as the columns in Column Names; for example:

('1'), ('1', 'z')

Examples

See Detecting Constraint Violations in the Administrator's Guide.