Vertica Primary Key Strange behavior

knows_not_much

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

NorbertK

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.