Vertica Primary Key Strange behavior

I wrote this simple piece of code


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


  • Options

    This question was posed on StackOverflow. My answer from the question:


    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.

Leave a Comment

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