Duplicate Primary Key

joergschaber

Hi, I am using the vertica-docker image and access Vertica using the jdbc-driver with DBVisualizer. I created an example table with
CREATE TABLE Persons (PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255)), PRIMARY KEY(PersonID));
Then I inserted the same row twice:
INSERT INTO public.Persons VALUES(1, 'Max', 'Muster', 'Street_1', 'Berlin');
INSERT INTO public.Persons VALUES(1, 'Max', 'Muster', 'Street_1', 'Berlin');
and it worked.
Select * from public.Persons

PersonID LastName FirstName Address City
1 Max Muster Street_1 Berlin
1 Max Muster Street_1 Berlin

Should that be prevented by the Primary key attribute?

marcothesane

You did not explicitly enable the primary key:
Immediately after creating your table, if you export the DDL, it looks like here below - note the DISABLED keyword:

SQL>select export_objects('','Persons',false)
export_objects


CREATE TABLE dbadmin.Persons
(
    PersonID int NOT NULL,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255),
    CONSTRAINT C_PRIMARY PRIMARY KEY (PersonID) DISABLED
);

Jim_Knicely

@joergschaber - There are 3 config parameters that determine if keys are enabled by default:

See:
https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/AdministratorsGuide/Constraints/ConstraintEnforcement/LevelsConstraintEnforcement.htm

Quick Example:

dbadmin=> CREATE TABLE test (c1 INT PRIMARY KEY);
CREATE TABLE

dbadmin=> SELECT is_enabled FROM primary_keys WHERE table_name = 'test';
 is_enabled
------------
 f
(1 row)

dbadmin=> ALTER DATABASE DEFAULT SET EnableNewPrimaryKeysByDefault = 1;
ALTER DATABASE

dbadmin=> CREATE TABLE test2 (c1 INT PRIMARY KEY);
CREATE TABLE

dbadmin=> SELECT is_enabled FROM primary_keys WHERE table_name = 'test2';
 is_enabled
------------
 t
(1 row)

joergschaber

Wow, I like this forum! Quick replies.