Enabled Primary Key not reinforced in 7.2

Moderator: NorbertKrupa

Post Reply
EmmanuelB
Newbie
Newbie
Posts: 1
Joined: Wed Dec 09, 2015 11:28 am

Enabled Primary Key not reinforced in 7.2

Post by EmmanuelB » Wed Dec 09, 2015 11:34 am

Hi,

I have created a table like this :

Code: Select all

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

Code: Select all

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 ?

Beg1nner
Newbie
Newbie
Posts: 20
Joined: Fri Jul 24, 2015 8:46 pm

Re: Enabled Primary Key not reinforced in 7.2

Post by Beg1nner » Sat Dec 19, 2015 5:20 pm

Vertica does not enforce PK while loading to enable fast data loads. you have to manually check for PK violations.

Use this functions to check for violations.

https://my.vertica.com/docs/7.2.x/HTML/ ... ht=analyze contraints

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Enabled Primary Key not reinforced in 7.2

Post by NorbertKrupa » Sat Dec 19, 2015 5:59 pm

Beg1nner wrote:Vertica does not enforce PK while loading to enable fast data loads. you have to manually check for PK violations.

Use this functions to check for violations.

https://my.vertica.com/docs/7.2.x/HTML/ ... ht=analyze contraints
Actually, as of 7.2, Vertica does enforce PRIMARY and UNIQUE constraints.
Checkout vertica.tips for more Vertica resources.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Enabled Primary Key not reinforced in 7.2

Post by JimKnicely » Mon Feb 29, 2016 4:22 pm

FYI ...

A hot fix for this issue is available in Vertica 7.2.1-4, which was released on on 02/25/2016!

Issue: VER-43961
Component: Sessions
Description: Enabled primary keys and unique keys were not enforced on a command issued from a JDBC or ODBC prepared
statement. This issue has been resolved.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica Data Load”