Detecting Constraint Violations with ANALYZE_CONSTRAINTS

Use the ANALYZE_CONSTRAINTS function to manually validate table constraints.

Ways to Use ANALYZE_CONSTRAINTS

You can use ANALYZE_CONSTRAINTS instead of (or as a supplement to) automatic enforcement of primary key, unique key, and check constraints. For information on automatic enforcement, see Enforcing Primary Key, Unique Key, and Check Constraints Automatically.

If you do enable primary key, unique key, and check constraints, note that ANALYZE_CONSTRAINTS does not check whether the constraints are disabled or enabled. You can use ANALYZE_CONSTRAINTS where:

You can use ANALYZE_CONSTRAINTS to validate referential integrity of foreign keys.Vertica does not support automatic enforcement of foreign keys.

How to Use ANALYZE_CONSTRAINTS to Detect Violations

The ANALYZE_CONSTRAINTS function analyzes and reports on constraint violations within the current schema search path. To check for constraint violations:

See the examples in ANALYZE_CONSTRAINTS for more information.

How ANALYZE_CONSTRAINTS Differs from Automatic Constraint Enforcement

Use ANALYZE_CONSTRAINTS as a reporting mechanism to find constraint violations. ANALYZE_CONSTRAINTS differs from automatic enforcement in that it does not enforce constraints.

Automatic enforcement of primary key, unique key, and check constraints does enforce constraints.

If you have set your constraints for automatic enforcement, during a bulk load Vertica reports on one row and rolls back the statement that introduced the constraint violation. If you want to see a report for all constraint violations:

Impact of Floating Point Values In Primary Keys When Using ANALYZE_CONSTRAINTS

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 information on floating point type, see DOUBLE PRECISION (FLOAT).

Examples

If you provide the following inputs, Vertica returns one row, indicating one violation, because the same primary key value (10) was inserted into table t1 twice.

=> CREATE TABLE t1(c1 INT);
=> ALTER TABLE t1 ADD CONSTRAINT pk_t1 PRIMARY KEY (c1);
=> CREATE PROJECTION t1_p (c1) AS SELECT * 
  	FROM t1 UNSEGMENTED ALL NODES;
=> INSERT INTO t1 values (10);
=> INSERT INTO t1 values (10); --Duplicate primary key value
=> \x
Expanded display is on.

=> SELECT ANALYZE_CONSTRAINTS('t1');

-[ RECORD 1 ]---+--------
Schema Name     | public
Table Name      | t1
Column Names    | c1
Constraint Name | pk_t1
Constraint Type | PRIMARY
Column Values   | ('10')

In the preceding query, if you give the second INSERT statement any different value, the result is 0 rows (no violations).

In the following example, create a table that contains three integer columns, one a unique key and one a primary key. 

=> CREATE TABLE table_1(  
  a INTEGER,    
  b_UK INTEGER UNIQUE,
  c_PK INTEGER PRIMARY KEY
);

Insert some values into table table_1 and commit the changes:

=> INSERT INTO table_1 values (1, 1, 1);
=> COMMIT;

Run ANALYZE_CONSTRAINTS on table table_1. No constraint violations are reported:

=> SELECT ANALYZE_CONSTRAINTS('table_1');
(No rows)

Insert duplicate unique and primary key values and run ANALYZE_CONSTRAINTS on table table_1 again. Vertica returns two violations: one against the primary key and one against the unique key:

=> INSERT INTO table_1 VALUES (1, 1, 1);
=> COMMIT;
=> SELECT ANALYZE_CONSTRAINTS('table_1');

-[ RECORD 1 ]---+----------
Schema Name     | public
Table Name      | table_1
Column Names    | b_UK
Constraint Name | C_UNIQUE
Constraint Type | UNIQUE
Column Values   | ('1')
-[ RECORD 2 ]---+----------
Schema Name     | public
Table Name      | table_1
Column Names    | c_PK
Constraint Name | C_PRIMARY
Constraint Type | PRIMARY
Column Values   | ('1')

The following example shows how you can look for constraint violations on only the unique key in the table table_1, qualified with its schema name.

=> SELECT ANALYZE_CONSTRAINTS('public.table_1', 'b_UK'); 
 
-[ RECORD 1 ]---+---------
Schema Name     | public
Table Name      | table_1
Column Names    | b_UK
Constraint Name | C_UNIQUE
Constraint Type | UNIQUE
Column Values   | ('1')

(1 row)

The following example shows that you can specify the same column more than once; ANALYZE_CONSTRAINTS, however, returns the violation only once.

=> SELECT ANALYZE_CONSTRAINTS('table_1', 'c_PK, C_PK');  


-[ RECORD 1 ]---+----------
Schema Name     | public
Table Name      | table_1
Column Names    | c_PK
Constraint Name | C_PRIMARY
Constraint Type | PRIMARY
Column Values   | ('1')

The following example creates a new table, table_2, and inserts a foreign key and different (character) data types.

=> CREATE TABLE table_2 (
  x VARCHAR(3), 
  y_PK VARCHAR(4), 
  z_FK INTEGER REFERENCES table_1(c_PK));

Alter the table to create a multicolumn unique key and multicolumn foreign key and create superprojections:

=> ALTER TABLE table_2 
  ADD CONSTRAINT table_2_multiuk PRIMARY KEY (x, y_PK);
WARNING 2623:  Column "x" definition changed to NOT NULL
WARNING 2623:  Column "y_PK" definition changed to NOT NULL

The following statement inserts a missing foreign key (0) into table table_2 and commits the changes.

=> INSERT INTO table_2 VALUES ('r1', 'Xpk1', 0);
=> COMMIT;

Checking for constraints on the table table_2 in the public schema detects a foreign key violation:

=> SELECT ANALYZE_CONSTRAINTS('public.table_2');  

-[ RECORD 1 ]---+----------
Schema Name     | public
Table Name      | table_2
Column Names    | z_FK
Constraint Name | C_FOREIGN
Constraint Type | FOREIGN
Column Values   | ('0')

Now add a duplicate value into the unique key and commit the changes:

=> INSERT INTO table_2 VALUES ('r2', 'Xpk1', 1);
=> INSERT INTO table_2 VALUES ('r1', 'Xpk1', 1);
=> COMMIT;

Checking for constraint violations on table table_2 detects the duplicate unique key error:

=> SELECT ANALYZE_CONSTRAINTS('table_2');  


-[ RECORD 1 ]---+----------------
Schema Name     | public
Table Name      | table_2
Column Names    | z_FK
Constraint Name | C_FOREIGN
Constraint Type | FOREIGN
Column Values   | ('0')
-[ RECORD 2 ]---+----------------
Schema Name     | public
Table Name      | table_2
Column Names    | x, y_PK
Constraint Name | table_2_multiuk
Constraint Type | PRIMARY
Column Values   | ('r1', 'Xpk1')

Create a table with multicolumn foreign key and then create the superprojections:

=> CREATE TABLE table_3(
   	z_fk1 VARCHAR(3), 
   	z_fk2 VARCHAR(4));
=> ALTER TABLE table_3 
   ADD CONSTRAINT table_3_multifk FOREIGN KEY (z_fk1, z_fk2)
   REFERENCES table_2(x, y_PK);

Insert a foreign key that matches a foreign key in table table_2 and commit the changes:

=> INSERT INTO table_3 VALUES ('r1', 'Xpk1');
=> COMMIT;

Check for constraints on table table_3. The query detects no violations:

=> SELECT ANALYZE_CONSTRAINTS('table_3');
(No rows)

Add a value that does not match and commit the change:

=> INSERT INTO table_3 VALUES ('r1', 'NONE');
=> COMMIT;

Check for constraints on table table_3. The query detects a foreign key violation:

=> SELECT ANALYZE_CONSTRAINTS('table_3');  


-[ RECORD 1 ]---+----------------
Schema Name     | public
Table Name      | table_3
Column Names    | z_fk1, z_fk2
Constraint Name | table_3_multifk
Constraint Type | FOREIGN
Column Values   | ('r1', 'NONE')

To analyze all constraints on all tables, issue the following statement.

SELECT ANALYZE_CONSTRAINTS('');

Clean up your database:

=> DROP TABLE table_1 CASCADE;
=> DROP TABLE table_2 CASCADE;
=> DROP TABLE table_3 CASCADE;

To learn how to remove violating rows, see the DISABLE_DUPLICATE_KEY_ERROR function.