Reporting Constraint Violations

For enabled constraints, Vertica reports multiple constraint violations before roll back. It reports multiple violations for primary key and unique constraints in all circumstances (for example, when you are loading or moving data). However, Vertica behaves differently for check constraints.

Reporting Primary Key and Unique Constraint Violations

The following example shows multiple duplicates in a file named sampledatafile.tbl. The sample table named pktest includes an enabled primary key comprised of the column cost. Vertica reports the duplicates when you attempt to load the table.

VMart=> CREATE TABLE pktest(cost int constraint costcons primary key enabled,destination int,timemorn int,finish int);
CREATE TABLE

VMart=> copy pktest from '/home/dbadmin/test/sampledatafile.tbl' delimiter ',';

ERROR 6745:  Duplicate key values: 'cost=106' 
-- violates constraint 'public.pktest.costcons'

DETAIL:  Additional violations:
Constraint 'public.pktest.costcons':
duplicate key values: 'cost=110'; 'cost=114'; 'cost=115'; 'cost=116'; 'cost=117'; 
'cost=118'; 'cost=119'; 'cost=120'; 'cost=121'; 'cost=122'; 'cost=123'; 'cost=124'; 
'cost=125'; 'cost=135'; 'cost=136'; 'cost=137'; 'cost=138'; 'cost=139'; 'cost=150'; 
'cost=151'; 'cost=20'; 'cost=200'; 'cost=251'; 'cost=252'; 'cost=255'; 'cost=257'; 
'cost=258'; 'cost=261'; 'cost=263';


Note: there were additional errors

As shown in the example, Vertica provides more complete information on the first violation, including the name of the constraint that was violated:

ERROR 6745:  Duplicate key values: 'cost=106' 
-- violates constraint 'public.pktest.costcons

 For subsequent violations, it reports abbreviated detail, up to a maximum of 30 violations. If there are more than 30 violations, Vertica adds the note "there were additional errors" at the end of the list of violations:

DETAIL:  Additional violations:
Constraint 'public.pktest.costcons':
duplicate key values: 'cost=110'; 'cost=114'; 'cost=115'; 'cost=116'; 'cost=117'; 
'cost=118'; 'cost=119'; 'cost=120'; 'cost=121'; 'cost=122'; 'cost=123'; 'cost=124'; 
'cost=125'; 'cost=135'; 'cost=136'; 'cost=137'; 'cost=138'; 'cost=139'; 'cost=150'; 
'cost=151'; 'cost=20'; 'cost=200'; 'cost=251'; 'cost=252'; 'cost=255'; 'cost=257'; 
'cost=258'; 'cost=261'; 'cost=263';

Note: there were additional errors

Refer to the section Primary Key Constraints for general information on where Vertica applies enforcement for enabled primary key and unique constraints.

Reporting Multiple Check Constraints

For enabled check constraints, Vertica reports only one constraint violation before roll back with three exceptions:

Refer to the section Check Constraints for general information on where Vertica applies enforcement for enabled check constraints.

This example shows multiple check constraint violations using the Vertica function COPY_TABLE.

VMart=> create table a(a int);
CREATE TABLE
VMart=> create table b(a int check(a>5));
CREATE TABLE
VMart=> copy b from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 6
>> 7
>> 8
>> \.
VMart=> copy a from stdin;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 1
>> 2
>> 3
>> 4
>> 1
>> 2
>> 3
>> 4
>> \.
VMart=> select copy_table('a','b');
NOTICE 7636:  Validating enabled constraints on table 'public.b'...
ERROR 7231:  Check constraint 'public.b.C_CHECK' (b.a > 5) violation in table 'public.b': 'a=1'
DETAIL:  Additional violations:
Check constraint 'public.b.C_CHECK':
violations: 'a=2'; 'a=3'; 'a=4'