Vertica Primary Key Strange behavior

Question

I wrote this simple piece of code in vertica 7.1.2

select reenable_duplicate_key_error();
create table Person(id int PRIMARY KEY, firstname varchar(20));
insert into Person select 1, 'test1' union all select 1, 'test2' union all select 1, 'test3';

Now if I do a

select * from Person;

i see

 id | firstname
----+-----------
  1 | test1
  1 | test2
  1 | test3
(3 rows)

so it seems there is no effect of marking a key as primary key

Kermit · Accepted Answer · 2015-12-24T18:42:27.747

This is expected and documented behavior. Vertica does not enforce uniqueness on load (imagine trying to ingest 500GB and having to rollback due to a PK violation). You can use analyze_constraints before committing or upgrade to 7.2 where you can enable enforcement of PK. It is still important to use keys for referential integrity.

See my blog post on other ways to enforce uniqueness on load.

Update: As of 7.2, Vertica can automatically enforce primary and unique key constraints. See the documentation for more information.

Vertica Primary Key Strange behavior

1 Answers1