The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Enabled Primary Key not reinforced in 7.2

Hi, 

 

I have created a table like this : 

CREATE TABLE public.DIM_PAYS_TEST (
CODE varchar(3) NOT NULL,
CODE_NUM int NOT NULL,
ALPHA2 varchar(2) NOT NULL,
ALPHA3 varchar(3) NOT NULL,
LIBELLE_FR varchar(100) NOT NULL,
LIBELLE_EN varchar(100) NOT NULL,

CONSTRAINT PK_DIM_PAYS_TEST PRIMARY KEY (CODE_NUM) ENABLED

)
UNSEGMENTED ALL NODES
KSAFE 1 ;

And loaded some data.

 

When I try to load the same data again using an ETL tool like SAP BusinessObject Data Services (ODBC) or Talend (JDBC), the data are loaded and therefore duplicated even if the constraint is enabled. But if I try a simple 

 

INSERT INTO public.DIM_PAYS_TEST (CODE,CODE_NUM,ALPHA2,ALPHA3,LIBELLE_FR,LIBELLE_EN) VALUES ('894','894'::Integer,'ZM','ZMB','Zambie','Zambia') ;

using another JDBC compliant client (like DbVisualizer) then I get a PK violation error.

 

How come the PK is not reinforced with the ETL tools, is there a connection parameter to add in the client configuration ? 

Comments

  • Hi Emmanuel,

     

    Thanks for bringing this to our attention.  This is a bug in key constraint enforcement which affects commands from JDBC PreparedStatement objects.  We're working on a fix.

     

    Mark

    HP Vertica

  • Hi, 

     

    OK, please not that this also happens with the ODBC driver. 

     

    Thank you,

    E

  • This is fixed in Vertica 7.2.1-4, and in 7.2.2.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.